REPLACE and TRIM
Part of the Beyond the Basics section of Coddy's SQL journey — lesson 9 of 27.
REPLACE(s, find, with) replaces every occurrence of find in s with with:
REPLACE('555-123-4567', '-', '') -- '5551234567'TRIM(s) strips whitespace off both ends. Pass a second argument to trim other characters, and use LTRIM / RTRIM for one-sided trims:
TRIM(' hi ') -- 'hi'
TRIM('---hi---', '-') -- 'hi'These are how you clean column data inline without updating the table.
Challenge
EasyAvailable tables and columns:
<strong>contacts</strong>:<strong>id</strong>,<strong>phone</strong>
Each phone may include leading/trailing spaces and any number of - separators. Return id and a column clean with all spaces and dashes removed. Order by id.
Cheat sheet
REPLACE(s, find, with) replaces every occurrence of a substring:
REPLACE('555-123-4567', '-', '') -- '5551234567'TRIM(s) strips whitespace from both ends; pass a second argument to trim other characters. Use LTRIM / RTRIM for one-sided trims:
TRIM(' hi ') -- 'hi'
TRIM('---hi---', '-') -- 'hi'Functions can be nested to apply multiple cleanups at once:
SELECT REPLACE(TRIM(phone), '-', '') AS clean FROM contacts;Try it yourself
SELECT id,
-- strip spaces and dashes
FROM contacts
ORDER BY id
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Beyond the Basics
2String Functions
LENGTH, UPPER, LOWERSUBSTRINSTRREPLACE and TRIMConcatenating with ||Recap - Invoices