Replace, Trim and Missing Text
Clean messy text.
Real data is messy. Extra spaces, words that need swapping, and values that are missing. Three small functions handle most of this.

- 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.
SELECT title,
REPLACE(title, 'The ', '') AS short_title
FROM books
WHERE title LIKE 'The %'
LIMIT 3;โโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโ โ 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.
SELECT '[' || ' Hello ' || ']' AS before,
'[' || TRIM(' Hello ') || ']' AS after;โโโโโโโโโโโโโโโโโฌโโโโโโโโโโ โ 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.
SELECT first_name,
COALESCE(email, 'no email') AS email
FROM customers
WHERE customer_id <= 4;โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโโ โ 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.
3 customers with no city: show 'Unknown' instead of a blank
Show hint
COALESCE(city, 'Unknown')
Show solution
SELECT first_name,
last_name,
COALESCE(city, 'Unknown') AS city
FROM customers
WHERE city IS NULL
LIMIT 3;โโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโ โ 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