Cleaning data with SQL
Real data arrives messy: blanks that should be NULL, stray spaces, mixed capitals, numbers stored as text, the same person entered twice. Most of it can be fixed in a single query. The examples use a raw web-form import, adoption_requests.
Empty strings vs NULL
Forms often save missing values as '' instead of NULL. NULLIF(col, '') converts them, and COALESCE(…, 'default') fills in a fallback.
SELECT id, phone, NULLIF(phone, '') AS phone_or_null, COALESCE(NULLIF(phone, ''), 'no phone') AS contact FROM adoption_requests;
Normalising text
Compare text only after making it consistent: TRIM removes stray spaces and LOWER fixes case. Rebuild nice capitalisation with UPPER and SUBSTR.
SELECT pet_name, LOWER(TRIM(pet_name)) AS match_key, UPPER(SUBSTR(TRIM(pet_name), 1, 1)) || LOWER(SUBSTR(TRIM(pet_name), 2)) AS display FROM adoption_requests;
Text to numbers
Strip symbols, turn empty text into NULL, then CAST. The order matters: CAST('' AS INTEGER) errors in PostgreSQL and silently becomes 0 in SQLite, which would drag an average down.
SELECT id, budget, CAST(NULLIF(REPLACE(TRIM(budget), '$', ''), '') AS INTEGER) AS budget_usd FROM adoption_requests;
Finding and removing duplicates
Group by the normalised value to find repeats, and use ROW_NUMBER() to keep one row per group, here the most recent.
SELECT id, email, requested_on
FROM (
SELECT id, LOWER(TRIM(email)) AS email, requested_on,
ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY requested_on DESC) AS rn
FROM adoption_requests
) latest
WHERE rn = 1;Common mistakes
Counting
WHERE phone IS NOT NULLwhen blanks are stored as ''.Casting before cleaning, which errors in PostgreSQL or quietly gives zeros in SQLite.
De-duplicating on the raw value, so 'Ana@Mail.com' and 'ana@mail.com ' survive as two people.
Questions
Should I clean in SQL or fix the data?+
Both. Clean in the query to answer today's question; then fix the source (validation, constraints, an UPDATE) so tomorrow's data arrives clean.
What's PostgreSQL's DISTINCT ON?+
A PostgreSQL-only shortcut that keeps the first row per group: SELECT DISTINCT ON (email) … ORDER BY email, requested_on DESC. ROW_NUMBER works in both.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.