SQL String Functions Tutorial 🎯

Welcome to the SQL String Functions tutorial! In this lesson, we'll explore a variety of functions that work with strings, helping you manipulate, compare, and extract data efficiently. 📝 Note: These functions are essential for any real-world database project.

What are SQL String Functions? 💡 Pro Tip: Think of these functions as tools that help you work with text data.

Basic String Functions

CONCAT()

The CONCAT() function combines two or more strings into one.

sql

SELECT CONCAT('Hello', ' ', 'World');
-- Output: Hello World

LENGTH()

The LENGTH() function returns the number of characters in a string.

sql

SELECT LENGTH('Hello');
-- Output: 5

String Comparison Functions

LIKE

The LIKE operator is used to search for a specified pattern within a string.

sql

SELECT * FROM users WHERE name LIKE 'A%';
-- Output: Users with names starting with 'A'

SOUNDS LIKE

The SOUNDS LIKE function is used to match strings based on their phonetic sounds, rather than their literal spelling.

sql

-- This is not a standard SQL function, but it might be available in some databases
SELECT * FROM customers WHERE name SOUNDS LIKE 'john';
-- Output: Customers with names sounding like 'John' (e.g., 'Jon', 'Jean')

String Extraction Functions

SUBSTRING()

The SUBSTRING() function extracts a portion of a string based on its position and length.

sql

SELECT SUBSTRING('HelloWorld', 1, 5);
-- Output: Hello

LEFT() and RIGHT()

The LEFT() function returns the specified number of characters from the beginning of a string, while the RIGHT() function returns the specified number of characters from the end.

sql

SELECT LEFT('HelloWorld', 3);
-- Output: Hell
SELECT RIGHT('HelloWorld', 5);
-- Output: World

String Manipulation Functions

LOWER() and UPPER()

The LOWER() function converts all the characters in a string to lowercase, while the UPPER() function converts all the characters to uppercase.

sql

SELECT LOWER('HELLO WORLD');
-- Output: hello world
SELECT UPPER('HELLO WORLD');
-- Output: HELLO WORLD

REPLACE()

The REPLACE() function replaces specified characters or substrings in a string.

sql

SELECT REPLACE('HelloWorld', 'World', 'Universe');
-- Output: HelloUniverse

Quiz

By mastering these SQL string functions, you'll be well-prepared to handle a wide range of text-related tasks in your database projects. Keep practicing and happy coding! 💪