SQL String Handling: SUBSTRING, REGEXP, CONCAT

Why string handling matters for analysts

Real-world data is messy. The Customers table arrives with names in all caps, phone numbers with country codes, emails with typos, and product codes glued together. You cannot group or join on dirty text, so string functions are the first line of cleaning. I reach for them before any dashboard because a wrong join on 'NYC' versus 'nyc' silently drops half the rows.

The three functions I use most are SUBSTRING to slice, REGEXP to match, and CONCAT to assemble. Learn these and most cleaning tasks stop being scary.

SUBSTRING: slice a piece of text

SELECT SUBSTRING(phone, 1, 3) AS area_code
FROM Customers

In Postgres and SQL Server the start index is 1;

SELECT SUBSTRING(sku, 4, 5) AS product_code
FROM Products
WHERE sku LIKE 'US-%'

SELECT SUBSTRING(email, 1, LENGTH(email) - 4) AS local_part
FROM Customers

REGEXP: match patterns, not just substrings

SELECT email
FROM Customers
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

This keeps only rows that look like real addresses and drops 'john@' or 'john@x'. MySQL and Postgres both support REGEXP;

SELECT *
FROM Products
WHERE name REGEXP 'pro|max|plus'

CONCAT: build and join text

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM Customers

SELECT CONCAT(region, '-', order_id) AS join_key
FROM Orders

Cleaning a messy import with string functions

SELECT UPPER(region)
FROM Orders

SELECT TRIM(customer_name)
FROM Customers

SELECT REPLACE(phone, '-', '')
FROM Customers

The collation trap: why 'Smith' != 'smith'

Collation decides how text is compared. A case-insensitive collation treats 'Smith' and 'smith' as equal; a case-sensitive one does not, and your JOIN or GROUP BY will split them. The default collation is set per database and per column, so two tables can disagree.

SELECT *
FROM Customers
WHERE last_name = 'smith' COLLATE SQL_Latin1_General_CP1_CI_AS

SELECT LOWER(last_name) COLLATE SQL_Latin1_General_CP1_CI_AS AS name, COUNT(*)
FROM Customers
GROUP BY LOWER(last_name) COLLATE SQL_Latin1_General_CP1_CI_AS
ORDER BY name

Worked example: clean and validate a customer list

Here is a runnable query that trims, lowercases, validates, and builds a clean key from the Customers table in one pass.

SELECT customer_id,
  LOWER(TRIM(email)) AS clean_email,
  CONCAT(TRIM(first_name), ' ', TRIM(last_name)) AS full_name
FROM Customers
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

Extracting parts with position functions

Beyond SUBSTRING, most engines offer LEFT, RIGHT, and POSITION to grab edges or find a delimiter. LEFT(email, 3) returns the first three characters; POSITION('@' IN email) tells you where the domain starts so you can slice the domain with SUBSTRING(email, POSITION('@' IN email) + 1). This is how you split 'john@shop.com' into 'john' and 'shop.com' in one query.

In my experience splitting on POSITION is more reliable than counting characters by hand, because emails vary in length. A common mistake is an off-by-one when adding 1 to the position, which keeps the '@' inside the domain slice. Add 1 so the delimiter itself is dropped, and the same logic applies to splitting a SKU on a dash or a path on a slash. Bad emails alone can cost a business around $1,200 a year in bounced campaigns, so cleaning them pays off.

SELECT SUBSTRING(email,
  1,
  POSITION('@' IN email) - 1) AS local_part,
  SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM Customers
WHERE email LIKE '%@%'

Turning text dates into real dates

Messy exports often store dates as text like '01/05/2025'. Cast them with TO_DATE in Postgres or STR_TO_DATE in MySQL so you can use DATE_TRUNC later. Until a text column is a real date type, sorting puts '02/01/2025' before '10/01/2025' as if it were text, which corrupts every time-based report built on top of it.

I recommend validating the format with a regex before casting, because one row like 'NULL' or 'TBD' will throw the whole cast and return nothing. Filter those out first, or use TRY_CAST in SQL Server, which returns NULL instead of erroring on a bad value and lets the rest of the rows through.

SELECT STR_TO_DATE(order_date_text, '%d/%m/%Y') AS order_date
FROM Orders
WHERE order_date_text REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$'

Before you load cleaned text into a report, sample a few rows by eye, because a query that runs clean can still produce wrong values. I once lowercased emails for a join and watched 3 percent of customers vanish, only to find the source stored some addresses with trailing spaces that TRIM fixed but LOWER alone did not. A quick SELECT of ten raw and cleaned pairs would have caught it in seconds. I now keep a validation step that shows raw next to cleaned for a sample of rows, so a silent transformation error is visible before it reaches the dashboard. That check has paid for itself every time a form changes and starts sending data in a new shape.

Next steps and a quick checklist

TRIM and LOWER before any join, use SUBSTRING for fixed slices, REGEXP only for real patterns, and CONCAT with explicit separators. Always check the collation when two tables disagree on casing. Once your text is clean, feed it into GROUP BY and CASE WHEN to label and aggregate. Practice on a messy Customers or Products import until cleaning feels like a reflex rather than a project. Once your text is clean, feed it into SQL GROUP BY and HAVING and SQL CASE WHEN to label and aggregate. The advanced aggregate guide shows how clean strings power accurate segment counts.