SQL String Functions

SQL provides a set of built-in string functions for working with text values. These functions can change the case of text, extract part of a string, calculate its length, join strings together, remove unwanted spaces, and more. String functions are applied to column values or literal text directly inside a SQL query.

Think of string functions as text-editing tools available inside a database query — similar to what can be done in a word processor, but applied to database columns.

The Reference Table

1. UPPER() and LOWER()

UPPER() converts all characters in a string to uppercase. LOWER() converts all characters to lowercase.

SELECT FirstName, UPPER(FirstName) AS UpperName, LOWER(FirstName) AS LowerName
FROM Students;

Result:

Practical use: Standardising city names before comparing, since 'delhi', 'Delhi', and 'DELHI' are technically different strings but represent the same city.

SELECT * FROM Students WHERE LOWER(City) = 'delhi';

2. LENGTH() and CHAR_LENGTH()

LENGTH() returns the number of bytes in a string. CHAR_LENGTH() returns the number of characters. For standard English text, both return the same number.

SELECT FirstName, CHAR_LENGTH(FirstName) AS NameLength
FROM Students;

Result:

3. CONCAT()

CONCAT() joins two or more strings together into one. It is useful for combining first name and last name, or building readable output from separate columns.

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Students;

Result:

-- Concatenating more pieces
SELECT CONCAT(FirstName, ' ', LastName, ' (', City, ')') AS StudentInfo
FROM Students;

Sample result: Ravi Sharma (delhi)

4. CONCAT_WS()

CONCAT_WS(separator, str1, str2, ...) joins strings with a separator between each one. The separator is the first argument. This avoids manually adding a separator between each value.

SELECT CONCAT_WS(' - ', FirstName, LastName, City) AS Info
FROM Students;

Result: Ravi - Sharma - delhi

5. SUBSTRING() / SUBSTR()

SUBSTRING(string, start, length) extracts a portion of a string. The start position counts from 1 (not 0).

-- Extract first 3 characters of FirstName
SELECT FirstName, SUBSTRING(FirstName, 1, 3) AS ShortName
FROM Students;

Result:

-- Extract email domain (from position 6 onward for 'ravi@gmail.com')
SELECT Email, SUBSTRING(Email, INSTR(Email, '@') + 1) AS Domain
FROM Students;

6. LEFT() and RIGHT()

LEFT(string, n) returns the first n characters from the left. RIGHT(string, n) returns the last n characters from the right.

SELECT FirstName,
       LEFT(FirstName, 2)  AS First2Chars,
       RIGHT(FirstName, 2) AS Last2Chars
FROM Students;

Result:

7. TRIM(), LTRIM(), RTRIM()

TRIM() removes leading and trailing spaces from a string. LTRIM() removes only the leading (left) spaces. RTRIM() removes only the trailing (right) spaces. These are essential for cleaning data that was entered with accidental spaces.

-- The email for Ravi has leading/trailing spaces: '  ravi@gmail.com  '
SELECT Email, TRIM(Email) AS CleanEmail
FROM Students
WHERE StudentID = 1;

Result: ' ravi@gmail.com ' becomes 'ravi@gmail.com'

8. REPLACE()

REPLACE(string, find, replace_with) finds all occurrences of a substring and replaces them with another string.

-- Replace 'gmail' with 'outlook' in the email
SELECT Email, REPLACE(Email, 'gmail.com', 'outlook.com') AS NewEmail
FROM Students;

Result for Ravi: ravi@gmail.com → ravi@outlook.com

9. INSTR()

INSTR(string, substring) returns the position (starting from 1) where a substring first appears. Returns 0 if not found.

SELECT Email, INSTR(Email, '@') AS AtSymbolPosition
FROM Students;

Result for 'ravi@gmail.com': Position 5 — the '@' is at the 5th character.

10. LPAD() and RPAD()

LPAD(string, total_length, pad_string) pads the left side of a string with a specified character until it reaches the total length. RPAD() pads the right side.

-- Pad StudentID with leading zeros to always show 4 digits
SELECT LPAD(StudentID, 4, '0') AS FormattedID, FirstName
FROM Students;

Result:

11. REVERSE()

REVERSE(string) reverses all the characters in a string.

SELECT FirstName, REVERSE(FirstName) AS Reversed
FROM Students;

Result: Ravi → ivaR, Priya → ayirP

Quick Reference Table

Combining Multiple String Functions

-- Create a clean formatted display name
SELECT CONCAT(
    UPPER(LEFT(FirstName, 1)),
    LOWER(SUBSTRING(FirstName, 2)),
    ' ',
    UPPER(LEFT(LastName, 1)),
    LOWER(SUBSTRING(LastName, 2))
) AS ProperName
FROM Students;

This formats the name in proper case — first letter uppercase, rest lowercase — regardless of how the data was originally entered.

Key Points to Remember

• String functions do not change the actual data in the table — they only affect the query output.
• String positions in SQL start at 1, not 0.
• String functions can be nested — one function can be used inside another.
• TRIM() is essential for cleaning up user-entered data that may have accidental spaces.
• Use LOWER() when comparing string values to make comparisons case-insensitive.

Summary

SQL string functions are tools for transforming, cleaning, and extracting information from text values inside queries. From converting case with UPPER() and LOWER(), to extracting parts with SUBSTRING(), joining with CONCAT(), and cleaning with TRIM() — these functions are essential for working with real-world text data that is rarely perfectly formatted.