Level 5 · Customers by state, cleaned up

Text typed by people or synced between systems is messy. In Harbor & Pine’s customer table, the state column contains values like 'CA', ' CA' and 'ca'. To the database these are three different strings, so GROUP BY state produces three rows for California and every count is too low.

The cleaning functions

function	does	example
TRIM(s)	removes leading and trailing spaces	' CA ' → 'CA'
LTRIM(s), RTRIM(s)	removes from one side only
UPPER(s)	converts to uppercase	'ca' → 'CA'
LOWER(s)	converts to lowercase	'Owen@ICloud.com' → 'owen@icloud.com'

These exist with the same names in DuckDB, Postgres, MySQL and BigQuery. Nest them to apply both:

SELECT UPPER(TRIM(state)) AS state, COUNT(*) AS customers
FROM customers
GROUP BY UPPER(TRIM(state));

Group by the cleaned expression, not by the raw column. Grouping by state and selecting UPPER(TRIM(state)) would still give separate groups that happen to display the same label, which is even more confusing.

Note the alias reuses the name state. In GROUP BY, Postgres resolves a bare state to the original column, not the alias, so writing out the expression (or GROUP BY 1) is the unambiguous choice.

Pick a canonical form

Decide a standard per column and apply it consistently: uppercase for state codes, lowercase for emails, and for city names something like INITCAP(TRIM(city)) in Postgres (DuckDB does not have INITCAP; comparing on LOWER(TRIM(city)) and displaying any one spelling is the usual workaround).

Find the mess first

Before you clean, look:

SELECT state, COUNT(*)
FROM customers
GROUP BY state
ORDER BY state;

Sorting puts the variants next to each other. LENGTH(state) <> 2 finds values with stray spaces. Real data can also hide non-breaking spaces or tabs that TRIM does not remove by default; DuckDB’s and Postgres’s TRIM(s, chars) and REGEXP_REPLACE(s, '\s+', '', 'g') handle those.

Clean in the query, fix at the source

Cleaning inside queries works, but every analyst has to remember to do it. If a column is consistently messy, raise it with the data engineering team so it is fixed once, upstream, in the pipeline or a cleaned model. Mention the counts: “45 customers have a state with stray spaces or lowercase” is a concrete ticket someone can act on.

Common mistakes

• Cleaning only in SELECT and not in GROUP BY or JOIN conditions. A join on c.state = r.state silently drops the messy rows; join on the cleaned expressions instead.
• Using LIKE comparisons without lowercasing, from chapter 2.
• Forgetting that TRIM(NULL) is NULL. Cleaning does not fill in missing values; that is COALESCE, which the boss level uses.