SQL String Functions

String functions let you clean, combine and extract text inside a query. Analysts use them constantly: standardising city names, pulling the domain out of an email, or building a display label.

Quick reference

Examples

1. Change case

SELECT customer_name,
       UPPER(customer_name) AS upper_name,
       LOWER(city) AS city_lower
FROM customers
LIMIT 3;

Result:

(3 rows)

2. Combine columns

Build a label like "Aarav Sharma (Bengaluru)":

SELECT customer_name || ' (' || city || ')' AS customer_label
FROM customers
LIMIT 3;

Result:

(3 rows)

CONCAT does the same and is safer with NULLs, because it treats them as empty text:

SELECT customer_name,
       customer_name || ' - ' || phone AS with_pipe,
       CONCAT(customer_name, ' - ', phone) AS with_concat
FROM customers
WHERE phone IS NULL;

Result:

(2 rows)

3. Extract part of a string

The first name is everything before the space:

SELECT customer_name,
       SPLIT_PART(customer_name, ' ', 1) AS first_name,
       LEFT(customer_name, 1) AS initial
FROM customers
LIMIT 4;

Result:

(4 rows)

4. Get the email domain

SELECT email,
       SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM customers
WHERE email IS NOT NULL
LIMIT 5;

Result:

(5 rows)

SUBSTRING with no length returns everything from the start position to the end.

5. Mask a phone number

Show only the last four digits, as you would on an order confirmation:

SELECT customer_name,
       'XXXXXX' || RIGHT(phone, 4) AS masked_phone
FROM customers
WHERE phone IS NOT NULL
LIMIT 3;

Result:

(3 rows)

6. Clean messy text

Real data often arrives with extra spaces and inconsistent case. TRIM + INITCAP fixes it:

SELECT '  new delhi ' AS raw_value,
       INITCAP(TRIM('  new delhi ')) AS cleaned,
       LENGTH('  new delhi ') AS raw_length;

Result:

(1 row)

7. Replace text

SELECT product_name,
       REPLACE(product_name, ' ', '-') AS url_slug
FROM products
LIMIT 3;

Result:

(3 rows)

Common mistakes

• || with a NULL column. One NULL makes the whole result NULL. Use CONCAT or COALESCE.
• Counting positions from 0. SQL strings start at position 1.
• Comparing untrimmed text. 'UPI ' does not equal 'UPI'. Trim before you compare or group.

Practice question

For each customer, show their name and the username part of their email (before the @). Skip customers without an email.

SELECT customer_name,
       SPLIT_PART(email, '@', 1) AS username
FROM customers
WHERE email IS NOT NULL
LIMIT 5;

Key takeaways

• UPPER, LOWER and INITCAP standardise case.
• || and CONCAT join text; CONCAT ignores NULLs.
• SUBSTRING, LEFT, RIGHT and SPLIT_PART extract pieces.
• TRIM and REPLACE clean messy values before analysis.