SQL text functions: clean messy data

LOWER(TRIM(email)) makes "ANNA@EXAMPLE.DE " and "anna@example.de" the same value, which is how you find duplicate customers.

SUBSTR(email, INSTR(email, '@') + 1) extracts the email domain. Use || to join text together.

Worked example

Normalise customer emails.

SELECT name, LOWER(TRIM(email)) AS clean_email
FROM customers
WHERE email <> LOWER(TRIM(email));

Common mistakes

• Cleaning only in the SELECT, then grouping by the raw column.
• Forgetting that trailing spaces make two values different.