Cleaning messy text data with SQL is not one function but a short pipeline: audit the column, trim the edges, collapse the runs of whitespace inside it, then split the compound fields into their parts. Every step is an expression, so the whole cleanup lives in a SELECT list and leaves the stored rows untouched until you deliberately write them back.
What does cleaning messy text data actually involve?
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 spacing and capitalisation. The episodes table below carries the result — the same table lessons 10 to 12 queried, extended with the columns this module works on. Watch the summary column: leading padding on row 1, a run of three spaces in the middle of row 2, a trailing space on 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
);
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. 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?
Yes, always. A cleanup query written blind is a guess, and the guess costs a full table scan every time it runs. The cheapest possible audit asks a yes/no question per row: is this value different from its trimmed twin? LENGTH measures characters, so the comparison is exact and 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 23
Two rows have padding at their edges. But TRIM removes spaces only at the ends, so the audit is incomplete on its own — row 2 has no edge padding and is still dirty. The second pass looks for interior runs of spaces, and the LIKE wildcard does the work: '% %' 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 square brackets is the cheapest debugging trick in SQL: it converts invisible whitespace into visible padding, so you can see exactly how many spaces and where. Two rules fall out of these queries. TRIM handles edges and nothing else, and LIKE finds patterns inside a string while REPLACE rewrites them.
What does the cleaning stack look like?
The stack is one nested expression, applied in the order that matters: fold the case first if you need it, collapse the interior runs, then trim the edges last so the trim cannot re-expose a space you 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]
Row 1 is clean: the interior run collapses and the edges vanish. Row 10 is clean: only the trailing space needed removing. Row 2 still shows a double space — and that is not a bug, it is the most important line in the lesson. REPLACE swaps every non-overlapping occurrence of two spaces with one space, scanning left to right; a run of three spaces yields a single replacement plus one leftover space. 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 arbitrary runs the honest answers are a recursive CTE (lesson 31) or a different ingestion rule that never lets a triple space into the column. Whichever route you take, the cleanup belongs at write time: a stack re-applied on every read is a hidden cost on every query.
Three of ten rows change under a single-pass cleanup — that number is the argument for fixing the import rather than patching every query forever.
How do you split a column that holds two things?
Compound columns are the other half of messy text. The air_time column holds a date and a clock in one string, and both halves are wanted independently — one for reporting by day, one for reporting by broadcast slot. SUBSTR with a fixed start and length cuts a fixed-layout field; 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:30
Positions are counted from one, so the date occupies characters 1 to 10 and the clock starts at 12. Episode 9 came from a back-catalogue import with no known air time, and a NULL input makes every string function return NULL (lesson 8), hence the filter. One more parsing idiom deserves a place here — INSTR locates the delimiter so 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 005
The dash sits at position 3 in every code, so the sequence number starts at 4. That hard-coded 4 would break the moment a code changed shape; the INSTR version asks the data where the separator is instead of assuming. Locating a delimiter 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. Define the clean shape once — as a view (lesson 49) or a CTE (lesson 30) — and 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-18
Three normalisations, each earning 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 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: compare LENGTH(value) with LENGTH(TRIM(value)) to find edge padding, and use LIKE '% %' to find interior runs.
- TRIM removes leading and trailing spaces only; REPLACE rewrites interior patterns; both nest into one expression.
- REPLACE is not idempotent for runs of three or more spaces — a second pass, or a better import rule, is required.
- SUBSTR with a fixed layout splits compound columns; INSTR finds the separator when the layout varies.
- Any string function fed a NULL returns NULL, so guard sparse columns with IS NOT NULL or COALESCE.
Cleanup drill: the runtime_label column holds values like '47 min' and '44min'. Write one query that returns the label, a cleaned label with the unit removed and the space normalised, and a numeric minutes column — and say aloud which function in your stack is doing the parsing and which is doing the arithmetic.