Cleaning Data with SQL

Tidy messy data with SQL: trim spaces, standardise capitals, find and remove duplicates, turn blanks into NULL, convert text to numbers and spot invalid values.

Lesson 16 of 102Intermediate17 min read

Data entered by people, imported from spreadsheets or collected from forms is almost never tidy. Names have extra spaces at the start, emails are typed in capitals, one city appears as "leeds", "Leeds " and "LEEDS", someone types "n/a" where a number should be, and the same person signs up twice. Every one of these breaks searches, counts and joins.

Analysts often say that most of their time goes into cleaning data before they can analyse it. SQL is an excellent tool for the job: you can inspect the problems with SELECT, fix thousands of rows with a single UPDATE, and remove duplicates with one DELETE.

In this lesson you'll work through a messy signups table, standardising text, finding and removing duplicates, converting blanks and invalid values, and following a safe routine that means you never damage good data while fixing bad data.

The messy signups table

idnameemailcityage
1 ana silvaAna@Example.comlisbon34
2Ben Hartben@example.comLeeds 29
3ana silvaana@example.comLisbon34
4Chloe Rouxchloe@exampleLYON(blank)
5Dev Shahdev@example.comleedsn/a

Spot the problems: spaces before and after values, inconsistent capitals, a duplicate person (rows 1 and 3), an email with no domain ending, a blank age and an age of "n/a". Each needs a different fix.

Standardising text

FunctionDoesExample
TRIM(x)Removes spaces from both ends' ana ' to 'ana'
LOWER(x) / UPPER(x)Changes case'Ana@X.com' to 'ana@x.com'
REPLACE(x, a, b)Replaces textREPLACE(phone, ' ', '')
SUBSTR(x, start, len)Takes part of the textSUBSTR('lyon', 1, 1) is 'l'

Combine them for the result you want. Emails are usually stored in lowercase, because Ana@Example.com and ana@example.com are the same address. For a city, a capital first letter and lowercase for the rest gives Lyon. PostgreSQL has INITCAP() for this; in SQLite and MySQL you build it from SUBSTR, UPPER and LOWER.

Finding and removing duplicates

Duplicates are found by grouping on whatever makes a record unique, here the cleaned email, and keeping groups with more than one row:

SQL
SELECT LOWER(TRIM(email)) AS email, COUNT(*) AS copies
FROM signups
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

To delete the extra copies, keep the row with the lowest id in each group and delete the others. In databases that support it, ROW_NUMBER() from the window functions lesson gives even more control, such as keeping the most recent copy instead.

Blanks, numbers and invalid values

  • NULLIF(TRIM(x), '') turns an empty string into NULL, so missing values are treated consistently.
  • CAST(x AS INTEGER) converts text to a number. Be careful: SQLite turns 'n/a' into 0 and MySQL gives a warning, while PostgreSQL stops with an error. Check values look numeric first.
  • LIKE finds values with the wrong shape. email NOT LIKE '%_@_%._%' finds emails without something@something.something.

After cleaning, prevent the problem coming back with constraints such as UNIQUE on email and CHECK (age BETWEEN 13 AND 120).

SQL Data Cleaning Syntax

Cleaning patterns

TRIM(col)  LOWER(col)  UPPER(col)  REPLACE(col, 'a', 'b')
NULLIF(TRIM(col), '')
CAST(col AS INTEGER)

SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1;

DELETE FROM t
WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY key);

SQL Data Cleaning Summary

  • TRIM, LOWER, UPPER, REPLACE and SUBSTR standardise text.
  • Find duplicates with GROUP BY on the cleaned key and HAVING COUNT(*) > 1.
  • Delete duplicates by keeping MIN(id) in each group.
  • NULLIF turns blanks into NULL; check text looks numeric before CAST.
  • Preview fixes with SELECT, then UPDATE inside a transaction, and add constraints afterwards.

SQL Data Cleaning Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Previewing the clean values

SQL
SELECT id,
       TRIM(name) AS name,
       LOWER(TRIM(email)) AS email,
       UPPER(SUBSTR(TRIM(city), 1, 1)) ||
         LOWER(SUBSTR(TRIM(city), 2)) AS city
FROM signups;
Output
id | name       | email           | city
---+------------+-----------------+-------
1  | ana silva  | ana@example.com | Lisbon
2  | Ben Hart   | ben@example.com | Leeds
3  | ana silva  | ana@example.com | Lisbon
4  | Chloe Roux | chloe@example   | Lyon
5  | Dev Shah   | dev@example.com | Leeds
Line-by-line explanation
  1. Line 2TRIM removes the spaces in front of row 1's name.
  2. Line 3Emails are trimmed and lowercased, so rows 1 and 3 now match exactly.
  3. Lines 4 to 5First letter upper case, the rest lower case. In MySQL, use CONCAT instead of ||.
Example 2

Finding problem values

SQL
SELECT id, email
FROM signups
WHERE email NOT LIKE '%_@_%._%';

SELECT id,
       age,
       CASE WHEN TRIM(age) <> '' AND TRIM(age) NOT GLOB '*[^0-9]*'
            THEN CAST(age AS INTEGER)
       END AS age_number
FROM signups;
Output
id | email
---+--------------
4  | chloe@example

id | age | age_number
---+-----+-----------
1  | 34  | 34
2  | 29  | 29
3  | 34  | 34
4  |     | NULL
5  | n/a | NULL
Line-by-line explanation
  1. Line 3The pattern needs at least one character, an @, more characters, a dot and more characters.
  2. Lines 7 to 8Convert only when the age is not blank and contains nothing but digits. GLOB is SQLite; MySQL uses age REGEXP '^[0-9]+$' and PostgreSQL age ~ '^[0-9]+$'.
  3. Line 9A CASE with no ELSE gives NULL, so blanks and "n/a" become NULL instead of a wrong 0.
Example 3

Fixing the table for good

SQL
UPDATE signups
SET name  = TRIM(name),
    email = LOWER(TRIM(email)),
    city  = UPPER(SUBSTR(TRIM(city), 1, 1)) || LOWER(SUBSTR(TRIM(city), 2));

DELETE FROM signups
WHERE id NOT IN (SELECT MIN(id) FROM signups GROUP BY email);

SELECT id, name, email, city FROM signups;
Output
id | name       | email           | city
---+------------+-----------------+-------
1  | ana silva  | ana@example.com | Lisbon
2  | Ben Hart   | ben@example.com | Leeds
4  | Chloe Roux | chloe@example   | Lyon
5  | Dev Shah   | dev@example.com | Leeds
Line-by-line explanation
  1. Lines 1 to 4The same expressions as the preview, now saved into the table.
  2. Lines 6 to 7For each email, keep the row with the smallest id and delete the rest. Row 3 is removed.
  3. Line 9Check the result. Chloe's email still needs a human to fix it.

Common SQL Data Cleaning Mistakes

Updating without previewing

Don't
UPDATE signups SET city = UPPER(city);    -- straight into the real data
Do
SELECT city, UPPER(city) AS new_city FROM signups;   -- check first
-- then run the UPDATE inside a transaction

Finding duplicates on raw values

Don't
SELECT email, COUNT(*) FROM signups GROUP BY email HAVING COUNT(*) > 1;
-- misses 'Ana@Example.com' and 'ana@example.com'
Do
SELECT LOWER(TRIM(email)), COUNT(*) FROM signups
GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1;

Casting text blindly

Don't
SELECT AVG(CAST(age AS INTEGER)) FROM signups;   -- 'n/a' and '' count as 0
Do
SELECT AVG(CAST(NULLIF(TRIM(age), '') AS INTEGER))
FROM signups
WHERE TRIM(age) NOT GLOB '*[^0-9]*';

SQL Data Cleaning in Real Projects

Data cleaning at work

Before a mailing goes out, marketing teams deduplicate contact lists so nobody gets the same email twice. Analysts standardise city and country names before building maps, and finance teams strip currency symbols and spaces from imported numbers. Data pipelines often run a cleaning step in SQL every night, loading raw data into one table and writing the cleaned version into another.

SQL Data Cleaning FAQ

Should I clean data in SQL or in a spreadsheet or Python?

Clean it where it lives when you can. SQL updates are repeatable, can be reviewed and work on millions of rows without copying data around. Spreadsheets suit a one-off look at a small file, and Python or similar tools help with complex parsing; even then, write the cleaned result back with SQL and keep the steps in a script.

How do I standardise phone numbers or codes?

Remove the characters that vary, then compare. Chained REPLACE calls work everywhere: REPLACE(REPLACE(REPLACE(phone, ' ', ''), '-', ''), '(', ''). PostgreSQL's regexp_replace(phone, '[^0-9+]', '', 'g') keeps only digits and the plus sign in one step. Store the cleaned value in a consistent format.

How do I find text values that aren't valid numbers?

In PostgreSQL, use a pattern: WHERE amount_text !~ '^[0-9]+(\.[0-9]+)?$'. In SQLite, WHERE amount_text GLOB '*[^0-9.]*' finds values containing anything other than digits and full stops. Review these rows before converting the column with CAST.

How do I stop messy data coming back?

Add constraints once the data is clean: NOT NULL for required columns, UNIQUE on values such as email addresses, CHECK rules for ranges and allowed values, and foreign keys for references. Validate input in the application too, so users get friendly messages before the database rejects anything.

Should I keep the original values before cleaning?

Yes. Copy the raw table first, CREATE TABLE signups_raw AS SELECT * FROM signups;, or add cleaned values in new columns rather than overwriting. If a cleaning rule turns out to be wrong, you can rerun it from the original data instead of guessing what was lost.

SQL Data Cleaning Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query to remove spaces from both ends of each name.

SELECT (name) FROM signups;
Multiple choiceeasy

Which clause keeps only groups that appear more than once?

Predict the outputmedium

What does SELECT NULLIF(TRIM(' '), ''); return?

Multiple choicemedium

Why lowercase emails before looking for duplicates?

Multiple choicehard

Which DELETE keeps one copy of each duplicated email?

SQL Data Cleaning Quiz

5 questions to check what you learned.

  1. What does LOWER('LYON') return?

    • LYON
    • lyon
    • Lyon
    • l

  2. What's the safest first step before an UPDATE that cleans data?

    • Drop the table
    • Run the same expressions in a SELECT and check the results
    • Turn off constraints
    • Delete duplicates

  3. In SQLite, what does CAST('n/a' AS INTEGER) return?

    • 0
    • NULL
    • An error
    • 'n/a'

  4. After cleaning a column of emails, how do you stop duplicates returning?

    • Run the cleanup every day
    • Use UNION
    • Add an index on id
    • Add a UNIQUE constraint on the email column

  5. Which pattern finds emails missing a dot after the @?

    • email LIKE '%@%'
    • email = '%.%'
    • email NOT LIKE '%_@_%._%'
    • email NOT LIKE '.'

Related SQL topics

06FunctionsTransform values inside a query with string, number, date and NULL-handling functions.08INSERT, UPDATE, DELETEAdd new rows, change existing data and remove rows safely, without losing anything by accident.04NULL ValuesUnderstand what NULL means, test for it with IS NULL, replace it with COALESCE, and avoid the surprises NULL causes in comparisons, counts and NOT IN.
Esc
↑↓ to navigateEnter to open