Chapter 03 · 10 min · Basic
One city, four spellings
Somebody asks for revenue by city. There is a city_name column. This looks like a GROUP BY and a coffee break.
Then you run it and get thirteen cities in a country where you were expecting four, because city_name is a free-text box and the column is really a record of everything anyone ever typed into it — including a trailing space, a full stop, and an abbreviation that only makes sense to the person who typed it.
This chapter is not a tour of TRIM and LOWER. You can look those up. It is about the two things that actually decide whether the cleanup survives contact with another human: you cannot fix what you have not enumerated, and the half that string functions cannot fix has to live somewhere reviewable.
The dataset
Fourteen rides with a free-text city_name, plus a city_aliases lookup table that maps a normalised spelling to a canonical city. The alias table is deliberately incomplete — one real city in the rides table is missing from it, and finding that gap is the last section of this chapter.
Schema
| ride_id | int |
| city_name | text |
| fare_myr | numeric(6,2) |
| raw_value | text |
| canonical_city | text |
Example data
Look at the whole list before you fix anything
The first query against a free-text column is never the report. It is the inventory.
Group by the raw value, count the rows, and read every line. This is boring and it is the step that decides whether everything after it is correct, because a cleanup rule you write before seeing the variants is a guess about data you have not looked at.
Order by frequency so the common spellings surface first, but read to the bottom — the long tail is where the surprises are. Notice what is in here: case differences, a leading and trailing space, a doubled internal space, a full stop, two genuine abbreviations, and a NULL.
Those are three different categories of problem, and only one of them is a string-function problem.
The country has four cities in this dataset. How many rows will this GROUP BY return?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to do when the data is dirty, duplicated and undocumented, and somebody still wants a number.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Normalise the mechanical half
- 03The half that is knowledge, not text
- 04Knowing when to stop
- 05Now do it without the inventory fitting on screen