Cleaning the messy text data with the SQL is not one function but a short pipeline. You audit the column, you trim the edges, you collapse the runs of the whitespace inside it, and then you split the compound fields into their parts. Every step is an expression, so the whole cleanup lives in a SELECT list and it leaves the stored rows untouched until you deliberately write them back.
The previous lesson in this module covered "Which String Functions Format Text?", and the next lesson in this module is about "What Do Number and Date Functions Compute?".
What does cleaning the messy text data actually involve?
The text is the only data type that arrives from the outside world without a contract. A podcast archive grew from a shared spreadsheet, a web form and two import scripts, and every source had its own idea of the spacing and the capitalisation. The episodes table below carries the result. It is the same table that lessons 10 to 12 queried, extended with the columns that this module works on. Watch the summary column. There is the leading padding on the row 1, there is a run of three spaces in the middle of the row 2, and there is a trailing space on the row 10.
CREATE TABLE episodes (
id INTEGER PRIMARY KEY,
title TEXT,
duration REAL,
downloads INTEGER,
released TEXT,
season INTEGER,
guest TEXT,
host TEXT,
genre TEXT,
episode_code TEXT,
runtime_label TEXT,
air_time TEXT,
summary TEXT,
guest_email TEXT
);The dirty text breaks things quietly. A trailing space turns = 'Kitchen chemistry at altitude' into a false negative, and a double space turns a phrase search into a miss. The cleaning is therefore not cosmetic. It is the difference between a filter that works and a filter that returns nothing at random.
Should you audit the column before you clean it?
You always do. A cleanup query that is written blind is a guess, and the guess costs a full table scan every time that it runs. The cheapest possible audit asks a yes-or-no question for each row. Is this value different from its trimmed twin? The LENGTH measures the characters, so the comparison is exact and it needs no pattern matching.
SELECT id, episode_code,
LENGTH(summary) AS raw_len,
LENGTH(TRIM(summary)) AS trimmed_len
FROM episodes
WHERE LENGTH(summary) <> LENGTH(TRIM(summary))
ORDER BY id;id episode_code raw_len trimmed_len
-- ------------ ------- -----------
1 EP-001 68 64
10 EP-010 24 23Two rows have the padding at their edges. But the TRIM removes the spaces only at the ends, so the audit is incomplete on its own. The row 2 has no edge padding and it is still dirty. The second pass looks for the interior runs of the spaces, and the LIKE wildcard does the work. The pattern '% %' means two spaces anywhere.
SELECT id, '[' || summary || ']' AS raw
FROM episodes
WHERE summary LIKE '% %'
ORDER BY id;id raw
-- ----------------------------------------------------------------------
1 [ Mapping the shore with LiDAR: a field study of coastal terrain. ]
2 [The boundary of a map where two survey lines disagree.]Wrapping the value in the square brackets is the cheapest debugging trick in the SQL. It converts the invisible whitespace into the visible padding, so you can see exactly how many spaces there are and where they are.
There are two rules that fall out of these queries. The TRIM handles the edges and nothing else. And the LIKE finds the patterns inside a string while the REPLACE rewrites them.
What does the cleaning stack look like?
The stack is one nested expression, applied in the order that matters. You fold the case first if you need it, you collapse the interior runs, and then you trim the edges last, so that the trim cannot re-expose a space that you have just removed.
SELECT id,
'[' || summary || ']' AS raw,
'[' || TRIM(REPLACE(summary, ' ', ' ')) || ']' AS cleaned
FROM episodes
WHERE id IN (1, 2, 10)
ORDER BY id;id raw cleaned
-- ---------------------------------------------------------------------- -----------------------------------------------------------------
1 [ Mapping the shore with LiDAR: a field study of coastal terrain. ] [Mapping the shore with LiDAR: a field study of coastal terrain.]
2 [The boundary of a map where two survey lines disagree.] [The boundary of a map where two survey lines disagree.]
10 [Naming wind and weather ] [Naming wind and weather]The row 1 is clean. The interior run collapses and the edges vanish. The row 10 is clean too, because only the trailing space needed removing. The row 2 still shows a double space, and that is not a bug. It is the most important line in the lesson.
The REPLACE swaps every non-overlapping occurrence of two spaces with one space, while it scans from the left to the right. A run of three spaces yields a single replacement plus one leftover space. The long runs need either a second pass or a different strategy.
SELECT id,
'[' || TRIM(REPLACE(REPLACE(summary, ' ', ' '), ' ', ' ')) || ']' AS twice_cleaned
FROM episodes
WHERE id = 2;id twice_cleaned
-- ---------------------------------------------------------------
2 [The boundary of a map where two survey lines disagree.]For the arbitrary runs, the honest answers are a recursive CTE, which lesson 31 covers, or a different ingestion rule that never lets a triple space into the column. Whichever route you take, the cleanup belongs at the write time. A stack that is re-applied on every read is a hidden cost on every query.
Three of the ten rows change under a single-pass cleanup. That number is the argument for fixing the import rather than patching every query for ever.
How do you split a column that holds two things?
The compound columns are the other half of the messy text. The column air_time holds a date and a clock in one string, and both of the halves are wanted independently. You need one for the reporting by the day, and one for the reporting by the broadcast slot.
The SUBSTR with a fixed start and a fixed length cuts a fixed-layout field, and the INSTR finds the separator when the layout varies.
SELECT id, air_time,
SUBSTR(air_time, 1, 10) AS air_date,
SUBSTR(air_time, 12) AS air_clock
FROM episodes
WHERE air_time IS NOT NULL
ORDER BY id
LIMIT 5;id air_time air_date air_clock
-- ---------------- ---------- ---------
1 2025-03-04 19:30 2025-03-04 19:30
2 2025-03-11 19:30 2025-03-11 19:30
3 2025-01-08 19:30 2025-01-08 19:30
4 2025-01-15 19:30 2025-01-15 19:30
5 2025-02-05 19:30 2025-02-05 19:30The positions are counted from one, so the date occupies the characters 1 to 10 and the clock starts at 12. The episode 9 came from a back-catalogue import with no known air time, and a NULL input makes every string function return a NULL, as lesson 8 explained. That is why the filter is there.
One more parsing idiom deserves a place here. The INSTR locates the delimiter, so that the cut point is not hard-coded.
SELECT id, episode_code,
INSTR(episode_code, '-') AS dash_at,
SUBSTR(episode_code, INSTR(episode_code, '-') + 1) AS seq_text
FROM episodes
ORDER BY id
LIMIT 5;id episode_code dash_at seq_text
-- ------------ ------- --------
1 EP-001 3 001
2 EP-002 3 002
3 EP-003 3 003
4 EP-004 3 004
5 EP-005 3 005The dash sits at the position 3 in every code, so the sequence number starts at 4. That hard-coded 4 would break the moment that a code changed its shape. The INSTR version asks the data where the separator is instead of assuming it.
Locating a delimiter and then slicing around it is the general recipe, and it is the opening move of lesson 15, where the extracted digits finally become a real number.
How should a cleaned column be used downstream?
Never by pasting the cleanup expression into every query that needs it. You define the clean shape once, as a view in lesson 49 or as a CTE in lesson 30, and you let every reader inherit it. The projection below is the whole idea in miniature.
SELECT id,
UPPER(episode_code) AS code,
LOWER(TRIM(genre)) AS genre_slug,
SUBSTR(air_time, 1, 10) AS air_date
FROM episodes
WHERE air_time IS NOT NULL
ORDER BY id
LIMIT 6;id code genre_slug air_date
-- ------ ----------- ----------
1 EP-001 science 2025-03-04
2 EP-002 science 2025-03-11
3 EP-003 photography 2025-01-08
4 EP-004 photography 2025-01-15
5 EP-005 photography 2025-02-05
6 EP-006 science 2025-03-18There are three normalisations, and each of them earns its place. The code is upper-cased because it is an identifier. The genre is lower-cased and trimmed because it becomes a join key and a group key in the later modules. And the air time is split because a date is not a timestamp.
Each transformation is applied once, in one visible place, and everything downstream compares like with like.
Quick reference
- Audit first. You compare LENGTH(value) with LENGTH(TRIM(value)) to find the edge padding, and you use the LIKE '% %' to find the interior runs.
- The TRIM removes the leading and the trailing spaces only, the REPLACE rewrites the interior patterns, and both of them nest into one expression.
- The REPLACE is not idempotent for the runs of three or more spaces, so a second pass or a better import rule is required.
- The SUBSTR with a fixed layout splits the compound columns, and the INSTR finds the separator when the layout varies.
- Any string function that is fed a NULL returns a NULL, so you guard the sparse columns with IS NOT NULL or with COALESCE.
Cleanup drill: the column runtime_label holds the values like '47 min' and '44min'. You write one query that returns the label, a cleaned label with the unit removed and the space normalised, and a numeric minutes column. Then you say aloud which function in your stack is doing the parsing and which one is doing the arithmetic.