Home › Databases › SQL Fundamentals

String Functions

Just as numeric functions simplify numerical operations, SQL's string functions are indispensable tools for manipulating and analyzing text data. These functions allow you to perform a wide range of operations, from simple concatenations to complex pattern matching, enabling you to derive meaningful insights from textual information.

Key String Functions in SQL

Let's explore some of the most essential string functions in SQL, complete with descriptions and practical examples to illustrate their usage.

1. CONCAT()

The CONCAT() function combines two or more strings into a single unit. This is particularly useful for merging columns or adding static text to query results.

Example:

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
-- Result: Hello World

2. SUBSTRING()

The SUBSTRING() function extracts a portion of a string based on specified starting position and length. It's ideal for parsing data or isolating specific parts of a string.

Example:

SELECT SUBSTRING('SQL Functions', 1, 3) AS SubStr;
-- Result: SQL

3. LENGTH()

The LENGTH() function returns the length of a string in characters. It's useful for validating data, such as ensuring inputs meet required lengths.

Example:

SELECT LENGTH('Data') AS Length;
-- Result: 4

4. UPPER() and LOWER()

UPPER(): Converts a string to uppercase.
LOWER(): Converts a string to lowercase.

These functions are essential for standardizing text data, especially when performing case-insensitive comparisons.

Example:

SELECT UPPER('hello') AS UpperCase, LOWER('WORLD') AS LowerCase;
-- Results: HELLO, world

5. REPLACE()

The REPLACE() function substitutes all occurrences of a specified substring within a string with another substring. It's useful for correcting or modifying data entries.

Example:

SELECT REPLACE('I love SQL', 'SQL', 'coding') AS NewString;
-- Result: I love coding

6. TRIM()

The TRIM() function removes leading and trailing spaces from a string. This is particularly useful for cleaning up data inputs and ensuring consistency.

Example:

SELECT TRIM('   SQL   ') AS TrimmedString;
-- Result: SQL

SQL String Functions

To provide a clearer overview, here's a detailed table outlining various string functions in SQL, complete with descriptions and example queries:

Sr	Function	Description	Query	Result
1	`CONCAT()`	Combines two or more strings into a single string.	SELECT CONCAT('Hello', ' ', 'World') AS Greeting;	Hello World
2	`SUBSTRING()`	Extracts a substring from a string starting at a specified position and length.	SELECT SUBSTRING('SQL Functions', 1, 3) AS SubStr;	SQL
3	`LENGTH()`	Returns the length of a string in characters.	SELECT LENGTH('Data') AS Length;	4
4	`UPPER()`	Converts a string to uppercase letters.	SELECT UPPER('hello') AS UpperCase;	HELLO
5	`LOWER()`	Converts a string to lowercase letters.	SELECT LOWER('WORLD') AS LowerCase;	world
6	`REPLACE()`	Replaces all occurrences of a specified substring within a string with another substring.	SELECT REPLACE('I love SQL', 'SQL', 'coding') AS NewString;	I love coding
7	`TRIM()`	Removes leading and trailing spaces from a string.	SELECT TRIM(' SQL ') AS TrimmedString;	SQL
8	`CHAR_LENGTH()`	Returns the number of characters in a string.	SELECT CHAR_LENGTH('MySQL') AS CharLength;	5
9	`LEFT()`	Returns the left part of a string with the specified number of characters.	SELECT LEFT('Database', 3) AS LeftPart;	Dat
10	`RIGHT()`	Returns the right part of a string with the specified number of characters.	SELECT RIGHT('MySQL', 3) AS RightPart;	SQL
11	`REPEAT()`	Repeats a string a specified number of times.	SELECT REPEAT('La', 3) AS RepeatedString;	LaLaLa
12	`POSITION()`	Returns the position of the first occurrence of a substring within a string.	SELECT POSITION('abc' IN 'abcdef') AS Position;	1
13	`LOCATE()`	Returns the position of the first occurrence of a substring within a string. Similar to `POSITION()`.	SELECT LOCATE('World', 'Hello World') AS LocatePos;	7
14	`INSTR()`	Returns the position of the first occurrence of a substring within a string. Similar to `POSITION()`.	SELECT INSTR('Hello World', 'World') AS InstrPos;	7
15	`LPAD()`	Pads the left side of a string with a specified character to a certain length.	SELECT LPAD('SQL', 5, '*') AS PaddedLeft;	**SQL
16	`RPAD()`	Pads the right side of a string with a specified character to a certain length.	SELECT RPAD('SQL', 5, '*') AS PaddedRight;	SQL**
17	`SUBSTRING_INDEX()`	Returns a substring from a string before a specified number of occurrences of a delimiter.	SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2) AS SubStrIdx;	www.mysql
18	`CONCAT_WS()`	Concatenates strings with a separator.	SELECT CONCAT_WS('-', '2024', '01', '01') AS DateFormatted;	2024-01-01

Learning with Interactive Exercises

To solidify understanding, incorporate interactive exercises and real-world projects where students can apply these string functions. For example:

Exercise 1: Create a query that concatenates a user's first and last name with a space in between.
```
SELECT CONCAT(first_name, ' ', last_name) AS FullName
FROM users;
```
Exercise 2: Develop a report that extracts the domain from each user's email address.
```
SELECT SUBSTRING_INDEX(email, '@', -1) AS Domain
FROM users;
```

🤖 Don't fully get this? Learn it with Claude

Stuck on String Functions? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.

🎨 Explain it visually

Build the mental picture, not memorization.

I just read a lesson on **String Functions** (Databases) and want to truly understand it. Explain String Functions from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🤔 Walk me through it (interactive)

Socratic — adapts to where you're stuck.

Teach me **String Functions** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧪 Quiz me & fix my gaps

Active recall exposes what you missed.

Quiz me on **String Functions** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧠 Make it stick

Intuition + hook + flashcards for long-term memory.

Help me remember **String Functions** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

📝 My notes

← Number Functions Date Functions →