sqlstreak

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 NULL when 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.

Practise it now