SQL text cleaning: normalize categories without merging different people

Text cleaning should preserve the meaning required by the analysis. Trimming whitespace and mapping approved category variants can improve a region report. Applying the same aggressive transformation to customer names or identifiers can merge different entities and corrupt the result.

The useful distinction is between normalizing a controlled label and resolving identity. The first may be governed by a small mapping table. The second often needs stable keys and additional evidence beyond similar-looking text.

The examples below are synthetic, self-contained SQLite queries. They can run through the commerce lab environment without modifying the source tables.

Preserve the raw value and create a candidate

WITH incoming(record_id, raw_region) AS (
    VALUES
      (1, ' North '),
      (2, 'north'),
      (3, 'NORTH'),
      (4, 'N. Region'),
      (5, ''),
      (6, NULL),
      (7, 'Northeast')
)
SELECT record_id, raw_region,
       NULLIF(LOWER(TRIM(raw_region)), '') AS candidate_region
FROM incoming
ORDER BY record_id;

The first three values become north. The fourth remains n. region, which requires an explicit mapping decision. Blank and NULL values become missing candidates. Northeast stays distinct.

Keeping raw_region allows a reviewer to see what changed. Replacing source values in place makes it harder to discover that a cleaning rule was too broad.

SQLite's built-in text functions have specific Unicode behaviour; do not assume LOWER performs full language-aware case folding for every script. Check the engine and collation used by your production system. SQLite core functions.

Use an approved mapping instead of broad guessing

WITH incoming(record_id, raw_region) AS (
    VALUES (1, ' North '), (2, 'N. Region'), (3, 'Northeast'), (4, '')
), normalized AS (
    SELECT record_id, raw_region,
           NULLIF(LOWER(TRIM(raw_region)), '') AS candidate
    FROM incoming
), region_map(candidate, canonical) AS (
    VALUES ('north', 'North'), ('n. region', 'North')
)
SELECT n.record_id, n.raw_region, m.canonical,
    CASE
        WHEN n.candidate IS NULL THEN 'Missing'
        WHEN m.candidate IS NULL THEN 'Needs mapping review'
        ELSE 'Mapped'
    END AS quality_status
FROM normalized AS n
LEFT JOIN region_map AS m ON m.candidate = n.candidate
ORDER BY n.record_id;

Records 1 and 2 map to North. Northeast requires mapping review, and the blank value is missing. We do not silently classify every string beginning with “north” as the same region.

The mapping table itself needs a uniqueness rule on the candidate key. Two rows for the same candidate could multiply source records during the join. Even a cleaning lookup can introduce the fan-out problem.

Do not normalize identifiers as though they were labels

Customer ID 0012 may be different from 12. Converting both to a number destroys the leading-zero representation and may merge keys. Similarly, punctuation can be meaningful in product codes, account references or externally assigned identifiers.

Store identifiers in a type that preserves their contract. If a source promises fixed-width digit strings, validate that shape rather than turning them into numeric measures. A customer ID is not a quantity to add or average.

Names require even more care. Removing spaces, accents or punctuation may make matching candidates easier to generate, but it does not prove that two records refer to the same person. Never overwrite stable identities based only on a cleaned display name.

Separate deterministic correction from uncertain matching

A controlled mapping such as N. Region → North can be approved by the data owner. A fuzzy suggestion such as “these two customer records might be the same” should carry a confidence or review status and preserve both original records until resolved.

For an analytical project, document which transformations are deterministic formatting rules, which are business mappings and which are unresolved hypotheses. This prevents a model or fuzzy matcher from quietly becoming the source of truth.

If AI proposes mappings, use it to generate candidates for review. Validate them against the controlled vocabulary and retain the approval record. A fluent explanation does not establish that Northeast and North are interchangeable.

Test for collisions and unmapped values

After normalization, count how many distinct raw values map to each canonical value. Multiple spellings can be intentional, but unexpected many-to-one mappings deserve inspection. Also report the share of records still missing or awaiting a mapping.

Reconcile record counts and monetary totals before and after enrichment. A label cleanup should not alter the number of orders or their values unless the task explicitly includes a separate eligibility change.

Version the mapping so historical reports can be reproduced. If a regional taxonomy changes, decide whether earlier reports should be restated or continue using the historical classification.

Exercise: add North-West to the input. Explain why a prefix-based rule could misclassify it, then add a reviewed canonical mapping that preserves the intended geography. Verify that the total input row count remains unchanged.

The Data Analytics with Generative AI course includes SQL, pandas and data verification. The same principle applies across tools: clean representations carefully, preserve provenance and do not confuse a convenient text match with verified identity.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Find duplicate business keys without deleting evidence.
• Continue with Build a SQL data dictionary from a reporting question.