Skip to content
Tutorials Center
My learning

Topics

Lesson 65 of 125 ยท Changing Data

Data Cleaning and Deduplication

Clean messy data with SQL. Trim and normalize text, turn placeholders into NULL, find duplicate rows with GROUP BY and ROW_NUMBER, then delete them safely.

Real data is messy. People type their email with capital letters, add a trailing space, write "N/A" in the phone field, or sign up twice. Imports from spreadsheets bring empty strings where a NULL belongs. None of this is a bug in SQL; it is the normal state of data that humans and many systems have touched. Left alone, it quietly breaks reports: '[email protected]' and '[email protected]' count as two customers, and WHERE phone IS NULL misses the rows that contain an empty string.

This lesson shows a safe routine for cleaning data in place. First look at the damage with SELECT. Then normalize values with text functions. Then find duplicates, choose which row survives, and only then delete the rest. Finally you'll add a constraint so the mess can't return. You'll use string functions, COALESCE and NULLIF, GROUP BY and HAVING, ROW_NUMBER, UPDATE and DELETE.

flowchart LR
  A["1. Profile<br/>SELECT and count problems"] --> B["2. Normalize<br/>trim, case, NULLIF"]
  B --> C["3. Find duplicates<br/>on the cleaned key"]
  C --> D["4. Choose survivors<br/>ROW_NUMBER rule"]
  D --> E["5. Delete extras<br/>preview first, in a transaction"]
  E --> F["6. Prevent<br/>UNIQUE index, CHECK"]

A cleaning routine: never delete before you have previewed exactly what will go.

All examples use this small, deliberately dirty contacts table. Study it before reading on: row 1 and 2 are the same person, as are rows 3 and 5, row 6 has an invalid email, and row 7 stores a placeholder instead of a missing phone.

Step 1: profile the mess

Before changing anything, measure. A handful of aggregate checks tells you what kinds of problems exist and how many rows they touch:

SQL How dirty is the data?

Press Run (or Ctrl + Enter) to see the result here.

Notice the last two columns: raw, there are 5 different email values (the NULL is ignored); after trimming and lowercasing there are only 4 distinct ones. Two spellings of one address were being counted as two customers. Also remember how NULL behaves: email <> LOWER(TRIM(email)) is NULL, not true, for a missing email, so those rows are not counted as "not normalized". That is what we want here, but be aware of it whenever you compare nullable columns.

Step 2: normalize

Now apply the fixes. The usual tools are:

Problem Tool
Leading and trailing spaces TRIM(x) (also LTRIM, RTRIM)
Inconsistent case LOWER(x) for emails and codes, UPPER(x) for country codes
Repeated inner spaces REPLACE(x, ' ', ' '), repeated or nested if needed
Formatting characters in phone numbers nested REPLACE calls (or regexp_replace where available)
Empty strings that mean "unknown" NULLIF(x, '')
Placeholder values CASE WHEN UPPER(x) IN ('N/A','NONE','-') THEN NULL ELSE x END

First try the transformations in a plain SELECT, so nothing is changed until you are happy with the output:

SQL Preview the cleaned values

Press Run (or Ctrl + Enter) to see the result here.

Every phone is now a bare string of 10 digits, row 3's empty phone and row 7's N/A have become NULL, and row 6 is flagged because its email has no dot after the @. Names are trimmed and their inner double space is collapsed, but row 2 stays lowercase; proper-casing names is awkward in SQL and usually belongs in the application. The LIKE pattern is a coarse sanity check, not real email validation; the only true test of an email address is sending something to it. For digits-only checks, SQLite offers phone NOT GLOB '*[^0-9]*', and other databases have regular expressions.

When the output looks right, make it permanent with one UPDATE that uses the same expressions. Because the new values come from the old ones, run it once, inside a transaction, and look at the result before you commit:

SQL
BEGIN;

UPDATE contacts
SET name    = REPLACE(TRIM(name), '  ', ' '),
    email   = LOWER(TRIM(email)),
    phone   = CASE
                WHEN NULLIF(TRIM(phone), '') IS NULL OR UPPER(phone) IN ('N/A', 'NONE', '-') THEN NULL
                ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), '.', ''), ' ', '')
              END,
    country = UPPER(country);

SELECT * FROM contacts;   -- inspect
COMMIT;                   -- or ROLLBACK; if anything looks wrong

On a large production table, keep a backup of the original columns (or the whole table) first, and run big updates in batches. See backup and maintenance.

Step 3: find the duplicates

Duplicates are defined by a key: the columns that decide two rows are "the same". Here that's the cleaned email. Always compute the key after normalization, or [email protected] and [email protected] will look different. GROUP BY ... HAVING COUNT(*) > 1 lists the keys that occur more than once:

SQL Which emails appear more than once?

Press Run (or Ctrl + Enter) to see the result here.

The table in this example already contains the normalized values from step 2, so the email key works directly. The first query tells you which emails are duplicated and how many copies exist. It can't tell you which row to keep, because GROUP BY collapses the group. The second query solves that with ROW_NUMBER() over a partition per email: rank 1 is the survivor, and every rank above 1 is a candidate for deletion.

The ORDER BY inside OVER is your survival rule, and it is a business decision: here "prefer the row that has a phone number, then the oldest id". Other typical rules are the most recently updated row, the row with the most filled-in columns, or the one referenced by other tables. Always add id as a final tiebreaker so the result is deterministic. Notice that for [email protected], the rule keeps row 5 (it has a phone) and not row 3.

Rows without an email (NULL) are skipped on purpose: a missing value is not a match, and PARTITION BY email would otherwise lump every row with no email into one group. For people without an email you might need a different key, such as name plus phone.

Step 4: delete the extras, safely

Turn the ranking into a delete by selecting the ids with rank greater than 1. Do it in two moves. First preview exactly the rows that will go, then run the same condition in a DELETE:

SQL Preview, delete, verify, prevent

Press Run (or Ctrl + Enter) to see the result here.

The preview returns ids 2 and 3, and the delete removes them, leaving five rows. The last statement fails with a UNIQUE constraint failed error. That error is the feature working: the database now refuses the duplicate that cleaning just removed. A unique index allows several NULL emails (a NULL is never equal to another NULL), so row 4 is untouched.

Never delete without a preview. A wrong partition key or a missing WHERE can remove thousands of good rows. Run the SELECT first, check the count, run the DELETE inside a transaction, compare the count again, and only then COMMIT. If other tables reference the deleted ids, re-point those foreign keys to the surviving row first, or the delete will fail or cascade into data you wanted to keep.

If your database doesn't allow a subquery on the table being deleted (MySQL is picky about this), wrap it in another derived table or use a DELETE ... JOIN form. Some databases also let you delete through the physical row identifier (ctid in PostgreSQL, ROWID in Oracle and SQLite) when the table has no primary key, which is handy for exact duplicate rows.

Step 5: stop the mess at the source

Cleaning is expensive; prevention is cheap. After cleaning, add constraints so bad data cannot enter:

  • NOT NULL where a value is required.
  • UNIQUE on natural keys such as email (store it already lowercased, or add a unique index on LOWER(email) where supported).
  • CHECK constraints for simple rules, such as CHECK (email = LOWER(TRIM(email))) or CHECK (phone IS NULL OR phone NOT GLOB '*[^0-9]*').
  • Foreign keys so references can't dangle.
  • Normalization in the application or in the import step, so the same cleaning rules run every time.

A cleaned table is also a good moment to rethink the schema: if "country" is free text, a lookup table with a foreign key is better than repairing typos forever (see normalization).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why should you normalize values (trim and lowercase) before searching for duplicates?

    Duplicates are compared by exact value. '[email protected]' and '[email protected] ' are different strings until they are cleaned, so the duplicate would be missed.
  2. 2. What does NULLIF(TRIM(phone), '') return for a phone containing only spaces?

    TRIM turns the value into an empty string, and NULLIF returns NULL when its two arguments are equal. This converts "blank" values into real NULLs.
  3. 3. Which tool lets you choose which duplicate row survives?

    GROUP BY and DISTINCT collapse the duplicates and hide the ids. ROW_NUMBER keeps every row and ranks them inside each key, so rank 1 is the survivor and the rest can be deleted.
  4. 4. What is the safest order of work for removing duplicates?

    Previewing the exact rows and checking the counts inside a transaction lets you roll back if the condition was wrong. A unique index would fail to build while duplicates exist.
  5. 5. After cleaning, what keeps duplicate emails from coming back?

    A unique index makes the database reject a second row with the same email. Cleanup jobs only fix the problem after the fact.

Summary

  • Profile first: count padded values, wrong case, empty strings, placeholders and duplicate keys with simple aggregates.
  • Normalize with TRIM, LOWER/UPPER, REPLACE, NULLIF and CASE; test the expressions in a SELECT before using them in an UPDATE.
  • Define duplicates by a cleaned key, list them with GROUP BY ... HAVING COUNT(*) > 1, and rank the copies with ROW_NUMBER() to choose a survivor.
  • Preview the rows, delete them inside a transaction, verify the counts, then commit.
  • Re-point foreign keys to the surviving row before deleting.
  • Prevent a repeat with UNIQUE, NOT NULL and CHECK constraints and by normalizing at the source.

Next: CREATE TABLE, where you start designing tables so that data arrives clean in the first place.