Cleaning Text
TRIM, LOWER, and UPPER.
Text cleaning uses functions you already know. Trim removes spaces at the start and end. Lower and upper fix capitals. Put them together, and messy text becomes consistent.

- TRIM removes spaces
- LOWER and UPPER fix capitals
- Messy becomes consistent
Clean every column
Trim the name. Trim the email, and make it lower case, because emails aren't case sensitive. For the city, we take the first letter in upper case, and the rest in lower case. Run it. Both Ana Lopez rows now have exactly the same email. And the cities look consistent. New York isn't perfect, but it's much better.
SELECT TRIM(name) AS name,
LOWER(TRIM(email)) AS email,
UPPER(SUBSTR(TRIM(city), 1, 1)) ||
LOWER(SUBSTR(TRIM(city), 2)) AS city
FROM signups;โโโโโโโโโโโโโฌโโโโโโโโโโโโโโโฌโโโโโโโโโโโ โ name โ email โ city โ โโโโโโโโโโโโโผโโโโโโโโโโโโโโโผโโโโโโโโโโโค โ ana lopez โ ana@mail.com โ Dublin โ โ Ben Ortiz โ ben@mail.com โ London โ โ ana lopez โ ana@mail.com โ Dublin โ โ Cara Diaz โ โ New york โ โ Dev Patel โ dev@mail.com โ โ โโโโโโโโโโโโโดโโโโโโโโโโโโโโโดโโโโโโโโโโโ
The golden rules
A few golden rules. Clean in a select first, and look at the result. Only then update the real table. Keep the original data if you can. And always compare text after trimming, and lowering it.
Summary
Trim, lower, and upper make text consistent. Next, the sneaky problem of blanks, and numbers stored as text.
Key takeaways- TRIM(text)
- LOWER(email)
- Clean in SELECT first