Cleaning Messy Data in SQL: The Patterns You Will Use Every Week

by

Nobody warns you about this part.

You learn joins, you learn aggregation, you land a job, and then you discover that the field called district contains “Pune”, “pune”, ” Pune “, “PUNE”, “Pune ” and “Punr”. And a number. And, for reasons nobody can explain, the word “test”.

Cleaning data is the majority of analytics work. The good news is that messy data is messy in surprisingly predictable ways, so you end up reusing the same eight or nine SQL patterns forever.

This article is those patterns.

Before you clean anything, look at the damage

The instinct is to start writing fixes. The better move is to spend ten minutes finding out what you are dealing with, because the shape of the mess decides the fix.

Start with a frequency count on any column you suspect:

sql

SELECT district_name, COUNT(*) AS row_count
FROM patients
GROUP BY district_name
ORDER BY row_count DESC;

This single query tells you more than any amount of staring at sample rows. You will immediately see the casing variants, the whitespace, the typos and the placeholder junk.

For numeric and date columns, check the edges:

sql

SELECT
    MIN(visit_date) AS earliest,
    MAX(visit_date) AS latest,
    COUNT(*)        AS total_rows,
    SUM(CASE WHEN visit_date IS NULL THEN 1 ELSE 0 END) AS null_dates
FROM visits;

If earliest comes back as 1900 or latest is three years in the future, someone has been typing dates by hand.

Pattern 1: Whitespace and casing

Two values that look identical on screen and refuse to group together are almost always separated by an invisible space.

sql

SELECT
    TRIM(district_name)                    AS district_trimmed,
    LOWER(TRIM(district_name))             AS district_key,
    COUNT(*)                               AS row_count
FROM patients
GROUP BY TRIM(district_name), LOWER(TRIM(district_name))
ORDER BY row_count DESC;

TRIM removes leading and trailing spaces. Combining it with LOWER gives you a normalised key you can group on safely.

Watch out for the ones TRIM does not catch: non breaking spaces, tabs and newlines that arrive from copy pasted spreadsheets.

sql

SELECT REPLACE(REPLACE(REPLACE(TRIM(district_name), CHAR(9), ''), CHAR(13), ''), CHAR(10), '') AS district_clean
FROM patients;

Ugly, effective, and worth wrapping in a view so you write it once.

Pattern 2: Empty strings pretending to be NULL

This one causes more wrong numbers than any other single issue. An empty string is not NULL. It is a value, it passes IS NOT NULL checks, and it quietly inflates your completeness metrics.

sql

-- Looks complete, is not
SELECT COUNT(*) AS rows_with_phone
FROM patients
WHERE phone_number IS NOT NULL;

sql

-- Honest version
SELECT COUNT(*) AS rows_with_phone
FROM patients
WHERE phone_number IS NOT NULL
  AND TRIM(phone_number) <> '';

The standard normalisation is to collapse all the “nothing” variants into real NULLs:

sql

SELECT
    patient_id,
    CASE
        WHEN TRIM(phone_number) IN ('', '-', 'NA', 'N/A', 'null', 'NULL', '0') THEN NULL
        ELSE TRIM(phone_number)
    END AS phone_clean
FROM patients;

Add to that list as you discover new creative ways people record “I do not have this information”.

Pattern 3: Removing duplicates properly

There are two kinds of duplicates and they need different treatment.

Fully identical rows can go with a simple grouping:

sql

SELECT patient_id, full_name, date_of_birth, COUNT(*) AS copies
FROM patients
GROUP BY patient_id, full_name, date_of_birth
HAVING COUNT(*) > 1;

Business duplicates, where the same person was registered twice with different IDs, are the real problem. The pattern here is a window function that ranks the candidates and keeps one.

sql

WITH ranked AS (
    SELECT
        patient_id,
        full_name,
        date_of_birth,
        phone_number,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY LOWER(TRIM(full_name)), date_of_birth
            ORDER BY created_at ASC
        ) AS rn
    FROM patients
)
SELECT patient_id, full_name, date_of_birth, phone_number
FROM ranked
WHERE rn = 1;

The PARTITION BY defines what “the same person” means. The ORDER BY decides which copy survives, in this case the oldest record.

Choose that ordering deliberately. Keeping the oldest preserves the original registration. Keeping the newest preserves the most recently corrected details. There is no universally right answer, so write down which one you chose and why, because somebody will ask in three months.

To see what you are about to discard, flip the filter to WHERE rn > 1 and eyeball it first. Always look before you delete.

Pattern 4: Standardising categories

Once you know your variants, CASE handles the mapping:

sql

SELECT
    patient_id,
    CASE
        WHEN LOWER(TRIM(district_name)) IN ('pune', 'punr', 'poona') THEN 'Pune'
        WHEN LOWER(TRIM(district_name)) IN ('mumbai', 'bombay')      THEN 'Mumbai'
        WHEN LOWER(TRIM(district_name)) IN ('', 'test', 'xxx')       THEN NULL
        ELSE INITCAP(TRIM(district_name))
    END AS district_clean
FROM patients;

If your database has no INITCAP, build it from UPPER, LOWER and SUBSTRING, or just map the known values explicitly.

A long CASE block is a sign you should be maintaining a lookup table instead:

sql

CREATE TABLE district_mapping (
    raw_value   VARCHAR(100) NOT NULL,
    clean_value VARCHAR(100),
    PRIMARY KEY (raw_value)
);

sql

SELECT
    p.patient_id,
    COALESCE(m.clean_value, INITCAP(TRIM(p.district_name))) AS district_clean
FROM patients AS p
LEFT JOIN district_mapping AS m
       ON LOWER(TRIM(p.district_name)) = LOWER(m.raw_value);

Now a non technical colleague can add mappings without touching your query. That is usually worth the setup cost.

Pattern 5: Dates stored as text

Sooner or later you inherit a column typed as VARCHAR holding things like 15/03/2026, 2026-03-15, March 15 2026 and 15-Mar-26.

Handle the dominant format first and isolate the rest:

sql

SELECT
    visit_id,
    raw_visit_date,
    CASE
        WHEN raw_visit_date REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
            THEN STR_TO_DATE(raw_visit_date, '%Y-%m-%d')
        WHEN raw_visit_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$'
            THEN STR_TO_DATE(raw_visit_date, '%d/%m/%Y')
        ELSE NULL
    END AS visit_date_parsed
FROM visits_raw;

Then count what failed:

sql

SELECT COUNT(*) AS unparsed
FROM visits_raw
WHERE raw_visit_date IS NOT NULL
  AND TRIM(raw_visit_date) <> ''
  AND STR_TO_DATE(raw_visit_date, '%Y-%m-%d') IS NULL
  AND STR_TO_DATE(raw_visit_date, '%d/%m/%Y') IS NULL;

If that number is small, list them and fix them by hand. If it is large, add another branch to the CASE.

One warning. 15/03/2026 and 03/15/2026 are both valid looking dates and only one is correct. Day and month ambiguity is the single most dangerous text to date problem, because it fails silently for the first twelve days of every month. Confirm the source system’s format with a human before you assume.

Pattern 6: Validating identifiers

Phone numbers, IDs and codes should match a known shape. Regular expressions in SQL let you check that cheaply.

sql

SELECT
    patient_id,
    phone_number,
    CASE
        WHEN RIGHT(REPLACE(REPLACE(TRIM(phone_number), '+', ''), ' ', ''), 10)
             REGEXP '^[6-9][0-9]{9}$'
            THEN RIGHT(REPLACE(REPLACE(TRIM(phone_number), '+', ''), ' ', ''), 10)
        ELSE NULL
    END AS phone_valid
FROM patients;

This strips the country code and spaces, then keeps the number only if it matches a valid ten digit Indian mobile pattern.

The same idea works for any coded field. Define the shape, test against it, and push anything that fails into a NULL or an exceptions table rather than letting it flow downstream.

There is a second layer worth adding. A number can be validly formatted and still be wrong, for example a field worker’s own phone entered for four hundred patients. Frequency counts catch this:

sql

SELECT phone_valid, COUNT(DISTINCT patient_id) AS patient_count
FROM patient_phone_clean
WHERE phone_valid IS NOT NULL
GROUP BY phone_valid
HAVING COUNT(DISTINCT patient_id) > 10
ORDER BY patient_count DESC;

Anything at the top of that list is a shared or placeholder number, not a household.

Pattern 7: A reusable data quality check

Rather than rediscovering problems every month, write one query that reports the health of a table and run it on a schedule.

sql

SELECT 'missing_district'   AS check_name,
       COUNT(*)             AS failing_rows
FROM patients
WHERE district_name IS NULL OR TRIM(district_name) = ''

UNION ALL

SELECT 'invalid_phone',
       COUNT(*)
FROM patients
WHERE phone_number IS NOT NULL
  AND RIGHT(REPLACE(REPLACE(TRIM(phone_number), '+', ''), ' ', ''), 10) NOT REGEXP '^[6-9][0-9]{9}$'

UNION ALL

SELECT 'future_dated_visit',
       COUNT(*)
FROM visits
WHERE visit_date > CURRENT_DATE

UNION ALL

SELECT 'duplicate_person_key',
       COUNT(*)
FROM (
    SELECT LOWER(TRIM(full_name)) AS name_key, date_of_birth
    FROM patients
    GROUP BY LOWER(TRIM(full_name)), date_of_birth
    HAVING COUNT(*) > 1
) AS dupes;

One result set, one row per check, easy to graph over time. When a number jumps, you know something changed upstream before anyone complains about the dashboard.

The rule that saves the most pain

Clean as close to the source as you can.

If the same CASE block is copy pasted into nine reports, it will drift. Someone will update seven of them. Two dashboards will disagree and nobody will be able to say which is right.

Options, in order of preference:

  1. Fix it at entry. A dropdown instead of a free text box eliminates the problem permanently. This is a conversation with the product team, not a SQL task, and it is the highest value thing you can do.
  2. Fix it in a view or a modelled table. Every downstream report reads the cleaned version.
  3. Fix it in each report. Fast today, expensive forever.

Most analysts live at level three because it is the only level they control. If that is you, at least keep the cleaning logic in one shared view and reference it, so there is a single place to change.

Wrapping up

Data cleaning is not glamorous and it will never be the part of the job you show off. But it is the part that decides whether the number on the dashboard is true.

Learn the patterns once: profile before you fix, collapse empty strings into NULL, rank and deduplicate with window functions, map categories through a lookup table, validate shapes with regex, and run a standing quality check so you find problems before your users do.

That toolkit covers the overwhelming majority of messy data you will ever meet.

Leave a Comment