Text columns are where data gets messy: extra spaces, mixed case, codes with prefixes, phone numbers in five formats. SQL string functions let you clean them as you query.
Function names vary a little between databases. The examples use common PostgreSQL / DuckDB style; most databases have direct equivalents.
The examples use a customers table from a fictional appliance service company, FixRight.
Remove extra spaces
SELECT TRIM(name) AS name FROM customers;TRIM removes leading and trailing spaces. LTRIM and RTRIM remove one side only. Hidden spaces are a frequent reason why WHERE city = 'Pune' misses rows.
Fix the case
SELECT UPPER(state_code), LOWER(email), INITCAP(name) FROM customers;Compare text case-insensitively by lowering both sides: WHERE LOWER(city) = 'pune'.
Extract part of a string
SELECT SUBSTRING(ticket_code, 1, 3) AS region_code FROM tickets; -- first 3 characters
SELECT RIGHT(phone, 10) AS phone_10 FROM customers; -- last 10 digitsFind a position
SELECT email,
SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM customers;Everything after the @ gives the email domain, useful for spotting company customers.
Replace characters
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS phone_clean FROM customers;Many databases also support regular expressions, for example REGEXP_REPLACE(phone, '[^0-9]', '', 'g') to keep only digits.
Combine text
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT city || ', ' || state AS location FROM customers;Watch out for NULL: with ||, a NULL part makes the whole result NULL in many databases. CONCAT usually treats NULL as empty. Use COALESCE when unsure.
Length
SELECT * FROM customers WHERE LENGTH(pincode) <> 6;A quick data-quality check: Indian PIN codes have six digits.
Pattern matching with LIKE
SELECT * FROM customers WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE sku LIKE 'AC-___';% matches any number of characters; _ matches exactly one. ILIKE (PostgreSQL) is the case-insensitive version.
Standardising categories
Combine string functions with CASE WHEN to merge spelling variants:
SELECT CASE
WHEN LOWER(TRIM(city)) IN ('bombay', 'mumbai') THEN 'Mumbai'
WHEN LOWER(TRIM(city)) IN ('bangalore', 'bengaluru') THEN 'Bengaluru'
ELSE INITCAP(TRIM(city))
END AS city_clean,
COUNT(*)
FROM customers
GROUP BY 1;A cleaning workflow
- Look at distinct values and their counts:
SELECT city, COUNT(*) ... GROUP BY city ORDER BY 2 DESC. - Note the patterns: spaces, case, spellings, prefixes.
- Write the cleaning expression and compare before vs after.
- Save it in a view or CTE so everyone uses the same clean version.
Practise
Our string functions set covers trimming, extracting, replacing and pattern matching, checked instantly.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: