AI Hints
Homeโ€บSQL Tutorialโ€บWorking with Text
SQL ยท Lesson 26 of 126

Replace, Trim and Missing Text

Clean messy text.

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

Real data is messy. Extra spaces, words that need swapping, and values that are missing. Three small functions handle most of this.

Replace, Trim and Missing Text
From the video: Replace, Trim and Missing Text
  • Real data is messy
  • Extra spaces, odd words, gaps
  • Three small fixes

REPLACE

Replace swaps one piece of text for another. Here we remove the word The from the start of titles, by replacing it with nothing. That's handy for sorting titles the way libraries do. Run it. Silent Orchard, Last Lighthouse, Quiet Kitchen.

query.sqlsql
SELECT title,
       REPLACE(title, 'The ', '') AS short_title
FROM books
WHERE title LIKE 'The %'
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title        โ”‚   short_title   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Silent Orchard  โ”‚ Silent Orchard  โ”‚
โ”‚ The Last Lighthouse โ”‚ Last Lighthouse โ”‚
โ”‚ The Quiet Kitchen   โ”‚ Quiet Kitchen   โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

TRIM

Trim removes spaces from the start and the end of text. We put square brackets around the result, so you can see the spaces. Run it. Before trim, there are spaces inside the brackets. After trim, they're gone. Spaces that sneak in from forms are a very common cause of failed matches.

query.sqlsql
SELECT '[' || '   Hello   ' || ']' AS before,
       '[' || TRIM('   Hello   ') || ']' AS after;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    before     โ”‚  after  โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ [   Hello   ] โ”‚ [Hello] โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

COALESCE fills gaps

Coalesce replaces a null with something you choose. Coalesce of email, and the words no email, gives the email if there is one, otherwise no email. Run it. Grace has no email, so she gets the label instead of a blank space.

query.sqlsql
SELECT first_name,
       COALESCE(email, 'no email') AS email
FROM customers
WHERE customer_id <= 4;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚         email         โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Tara       โ”‚ tara.brown1@mail.com  โ”‚
โ”‚ Grace      โ”‚ no email              โ”‚
โ”‚ Finn       โ”‚ finn.haddad3@mail.com โ”‚
โ”‚ Daniel     โ”‚ daniel.ali4@mail.com  โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Unknown cities

Your turn. Show the first name, last name, and city of three customers with no city. But instead of a blank, show the word Unknown. Pause and try. Here's one way. Coalesce of city and Unknown, named city.

โœ๏ธ Exercise: Unknown cities

3 customers with no city: show 'Unknown' instead of a blank

Show hint

COALESCE(city, 'Unknown')

Show solution
solution.sqlsql
SELECT first_name,
       last_name,
       COALESCE(city, 'Unknown') AS city
FROM customers
WHERE city IS NULL
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ last_name โ”‚  city   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Hugo       โ”‚ Kim       โ”‚ Unknown โ”‚
โ”‚ Priya      โ”‚ Tanaka    โ”‚ Unknown โ”‚
โ”‚ Will       โ”‚ Brown     โ”‚ Unknown โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Replace, trim, and coalesce clean up most messy text. Next, dates, which every business needs.

Key takeaways
  • REPLACE(text, old, new)
  • TRIM() removes edge spaces
  • COALESCE() fills NULLs
Next up: Working with Dates โ€” years, months, days