SQL String Functions: CONCAT, SUBSTRING, REPLACE, TRIM and String Problems

SQL string functions clean, combine and extract text, and they come up constantly in data validation: checking emails, normalising phone numbers, splitting names. Below are the functions themselves, then the classic string problems solved step by step.

String Functions

-- Length of string
SELECT name, LENGTH(name) AS name_length FROM employees;

-- UPPER and LOWER case
SELECT UPPER(name) AS name_upper, LOWER(email) AS email_lower FROM employees;

-- Substring: extract part of string
SELECT SUBSTRING(name, 1, 5) AS first_5_chars FROM employees;
SELECT SUBSTR(phone, 1, 3) AS area_code FROM employees;   -- Oracle

-- TRIM: remove whitespace
SELECT TRIM('   Naveed   ') AS clean_name;        -- "Naveed"
SELECT LTRIM('   hello') AS left_trimmed;          -- "hello"
SELECT RTRIM('hello   ') AS right_trimmed;         -- "hello"

-- REPLACE
SELECT REPLACE(phone, '-', '') AS clean_phone FROM employees;

-- CONCAT
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
SELECT first_name || ' ' || last_name AS full_name FROM employees;  -- Oracle/PG

-- INSTR / POSITION: find position of substring
SELECT INSTR(email, '@') AS at_position FROM employees;

-- LEFT and RIGHT: extract from start or end
SELECT LEFT(name, 3) AS initials FROM employees;
SELECT RIGHT(phone, 4) AS last_4 FROM employees;

-- LPAD / RPAD: pad string to a length
SELECT LPAD(emp_id, 6, '0') AS padded_id FROM employees; -- 000042

-- CHAR_LENGTH vs LENGTH (important for Unicode)
-- LENGTH: bytes; CHAR_LENGTH: characters
SELECT CHAR_LENGTH('café') AS chars;  -- 4 (MySQL)

String Aggregation: GROUP_CONCAT and STRING_AGG

-- GROUP_CONCAT (MySQL): combine names into one string
SELECT dept,
       COUNT(*) AS members,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS team
FROM employees GROUP BY dept;
-- Output: Engineering | 5 | Alice, Bob, Charlie, Naveed, Ravi

-- STRING_AGG (PostgreSQL / SQL Server)
SELECT dept, STRING_AGG(name, ', ' ORDER BY name) AS team
FROM employees GROUP BY dept;

-- Limit concat to top 3 names per dept (combine with RANK)
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT dept,
       GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ' | ') AS top3_earners
FROM ranked WHERE rn <= 3 GROUP BY dept;

Common SQL String Problems

Email Validation Pattern

-- Find users with INVALID email format
-- Valid: contains exactly one @, has domain with ., no spaces

-- MySQL: REGEXP (Regular Expression)
SELECT user_id, email FROM users
WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- Simpler version: check for @ and .domain
SELECT user_id, email FROM users
WHERE email NOT LIKE '%@%.%'
   OR email LIKE '% %'             -- contains space
   OR email LIKE '@%'              -- starts with @
   OR INSTR(email, '@') = 0        -- no @ at all
   OR LENGTH(email) - LENGTH(REPLACE(email, '@', '')) > 1; -- multiple @

-- PostgreSQL: use ~ for regex
SELECT user_id, email FROM users
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

Extract Domain from Email

-- Extract domain part from email (everything after @)
SELECT email,
       SUBSTRING(email, INSTR(email, '@') + 1) AS domain
FROM users;
-- naveed@gmail.com → gmail.com

-- Count users by email domain
SELECT
    SUBSTRING(email, INSTR(email, '@') + 1) AS domain,
    COUNT(*) AS user_count
FROM users
GROUP BY domain
ORDER BY user_count DESC;

-- Extract username (before @)
SELECT email,
       LEFT(email, INSTR(email, '@') - 1) AS username
FROM users;

-- Extract top-level domain (.com, .in, .org)
SELECT email,
       SUBSTRING_INDEX(email, '.', -1) AS tld
FROM users;
-- naveed@example.co.in → in

Phone Number Cleaning

-- Standardise phone numbers: remove spaces, dashes, brackets, +91
SELECT phone,
    REGEXP_REPLACE(
        REGEXP_REPLACE(phone, '^(\\+91|0091|91)', ''),  -- remove country code
        '[^0-9]', ''   -- remove all non-digits
    ) AS clean_phone
FROM users;

-- MySQL without REGEXP_REPLACE (chained REPLACE)
SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone,
    '+91',''), ' ',''), '-',''), '(',''), ')','') AS clean_phone
FROM users;

-- Validate: Indian mobile number (10 digits, starts with 6-9)
SELECT phone FROM users
WHERE phone REGEXP '^[6-9][0-9]{9}$';

-- Find duplicate phone numbers (after cleaning)
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS clean_phone, COUNT(*) AS cnt
FROM users
GROUP BY clean_phone
HAVING COUNT(*) > 1;

Parse Structured Strings

-- Parse CSV-like data stored in a single column
-- product_tags column: "electronics,mobile,smartphone,samsung"

-- Extract first tag
SELECT product_id,
       SUBSTRING_INDEX(product_tags, ',', 1) AS first_tag
FROM products;

-- Count number of tags (count commas + 1)
SELECT product_id,
       LENGTH(product_tags) - LENGTH(REPLACE(product_tags, ',', '')) + 1 AS tag_count
FROM products;

-- Find products containing 'mobile' tag
SELECT * FROM products
WHERE FIND_IN_SET('mobile', product_tags) > 0;
-- FIND_IN_SET(value, csv_string) returns position or 0 if not found

-- Extract URL components
-- url: https://www.example.com/products/123?ref=home
SELECT url,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url, '://', -1), '/', 1) AS domain,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', -1), '?', 1)   AS path_end
FROM pages;

Name Formatting Problems

-- Capitalise first letter of each word (Title Case)
-- No built-in INITCAP in MySQL (Oracle/PG have it)
-- MySQL approach for single-word names:
SELECT CONCAT(UPPER(LEFT(name,1)), LOWER(SUBSTRING(name,2))) AS formatted_name
FROM users;

-- Oracle / PostgreSQL:
SELECT INITCAP(name) AS formatted_name FROM users;

-- Split full name into first and last name
SELECT full_name,
    SUBSTRING_INDEX(full_name, ' ', 1)  AS first_name,
    SUBSTRING_INDEX(full_name, ' ', -1) AS last_name
FROM users;
-- "Naveed Khan" → first_name="Naveed", last_name="Khan"

-- Find users whose first name and last name are the same
SELECT full_name FROM users
WHERE SUBSTRING_INDEX(full_name, ' ', 1) = SUBSTRING_INDEX(full_name, ' ', -1);

FAQs

How do you extract the domain from an email in SQL?

In MySQL use SUBSTRING_INDEX(email, '@', -1); in PostgreSQL SPLIT_PART(email, '@', 2).

What is the difference between CHAR_LENGTH and LENGTH?

In MySQL, CHAR_LENGTH counts characters and LENGTH counts bytes, so they differ for multi-byte UTF-8 text.

How do you concatenate values from several rows into one string?

Use GROUP_CONCAT in MySQL or STRING_AGG in PostgreSQL and SQL Server, together with GROUP BY.