String Functions — SQL Tutorial

Common String Functions

SQL provides a rich set of string functions for manipulating text data. These functions allow you to combine, extract, transform, and search within text columns.

String functions are used in SELECT, WHERE, and UPDATE statements to process text data. They are invaluable for data cleaning, formatting output, and building search features.

CONCAT(a, b): Combines two or more strings together
SUBSTRING(str, start, length): Extracts part of a string
UPPER(str): Converts text to uppercase
LOWER(str): Converts text to lowercase
LENGTH(str): Returns the number of characters in a string
TRIM(str): Removes leading and trailing whitespace

Example

-- CONCAT: Combine first and last name
SELECT CONCAT('Hello', ' ', 'World') AS greeting;

-- UPPER and LOWER
SELECT 
    UPPER(username) AS upper_name,
    LOWER(email) AS lower_email
FROM users;

-- LENGTH: Find the length of usernames
SELECT username, LENGTH(username) AS name_length
FROM users
ORDER BY name_length DESC;

-- SUBSTRING: Extract first 3 characters
SELECT 
    username,
    SUBSTRING(username, 1, 3) AS short_name
FROM users;

-- TRIM: Remove whitespace
SELECT TRIM('   hello   ') AS trimmed;

Notes

In PostgreSQL, you can also use the || operator for concatenation: 'Hello' || ' ' || 'World'. In SQL Server, use the + operator.

REPLACE, LEFT, RIGHT & Practical Examples

REPLACE substitutes all occurrences of a substring within a string. LEFT and RIGHT extract a specified number of characters from the beginning or end of a string.

These functions are commonly used for data cleaning — fixing inconsistent data, standardizing formats, and masking sensitive information.

Example

-- REPLACE: Substitute text
SELECT REPLACE('Hello World', 'World', 'SQL') AS result;
-- Result: 'Hello SQL'

-- Replace domain in email addresses
SELECT 
    email,
    REPLACE(email, '@example.com', '@company.com') AS new_email
FROM users;

-- LEFT and RIGHT
SELECT 
    LEFT('Hello World', 5) AS left_result,   -- 'Hello'
    RIGHT('Hello World', 5) AS right_result;  -- 'World'

-- Mask email addresses for privacy
SELECT 
    CONCAT(
        LEFT(email, 2),
        '***@***',
        RIGHT(email, 4)
    ) AS masked_email
FROM users;

-- Find and filter using string functions
SELECT username FROM users
WHERE LENGTH(username) > 4
AND LOWER(username) LIKE 'a%';

Notes

String functions can be used in WHERE clauses, but applying a function to a column in WHERE can prevent the database from using indexes, which may slow down queries.