AI Hints
Homeโ€บSQL Tutorialโ€บCleaning Messy Data
SQL ยท Lesson 90 of 126

Cleaning Text

TRIM, LOWER, and UPPER.

โฑ 2 min readโ–ถ Video at 2:08:29๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Cleaning Text
From the video: Cleaning Text
  • 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.

clean.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   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.

Select firstlook before you change
Then UPDATEwhen you're sure
Keep originalsyou may need them
CompareTRIM + LOWER

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
Next up: Blanks and Numbers โ€” NULLIF and CAST