String and Date Normalization

Why Strings Break Analysis

Strings are the most common data type and the most common source of analysis errors. Unlike numbers, strings have no inherent ordering or equality semantics. 'New York', 'new york', 'NEW YORK', and 'New York ' are four different strings that refer to the same city. A GROUP BY on the raw column produces four groups instead of one.

This is not a theoretical problem. It is the most common cause of wrong counts in business reports. A sales report that groups by region will show separate rows for 'West', 'west', and 'West ', each with a fraction of the true West region total. The report looks detailed but is simply wrong.

The Normalization Principle

Normalization means mapping variant representations to a single canonical form. The canonical form is not inherently better than the variants; it is simply the one you choose and apply consistently. Once chosen, every value in the column must match it exactly.

The principle applies to all string data:

• Casing: 'USA', 'usa', 'Usa' → 'USA'
• Whitespace: ' John', 'John ', 'John\t' → 'John'
• Punctuation: 'Smith, John', 'John Smith' → one chosen format
• Abbreviations: 'CA', 'Calif.', 'California' → 'California'
• Units: '5 kg', '5kg', '5 kilograms' → '5 kg'

Casing and Whitespace

The simplest normalization fixes are trimming whitespace and standardizing casing. These two operations resolve the majority of string quality issues.

-- Trim whitespace and standardize casing
SELECT
    TRIM(region) AS region_clean,
    UPPER(TRIM(country)) AS country_clean,
    INITCAP(TRIM(city)) AS city_clean
FROM locations;

TRIM removes leading and trailing spaces. UPPER and LOWER standardize casing for codes and identifiers. INITCAP capitalizes the first letter of each word, which is appropriate for names and titles.

Why not just use LOWER for everything? Because presentation matters. If the column feeds a report, 'new york' looks unprofessional. Choose the canonical form based on how the data will be consumed: UPPER for country codes, INITCAP for place names, LOWER for email addresses.

Hidden Whitespace

Not all whitespace is visible. Tabs (\t), non-breaking spaces (\u00A0), and zero-width characters (\u200B) survive a simple TRIM and cause joins and groupings to fail.

-- Remove all whitespace characters including tabs and non-breaking spaces
SELECT REGEXP_REPLACE(city, '[\\s\\u00A0\\u200B]+', '', 'g') AS city_clean
FROM locations;

The regular expression [\\s\\u00A0\\u200B]+ matches any sequence of whitespace characters, including standard spaces, tabs, non-breaking spaces, and zero-width spaces. The 'g' flag replaces all occurrences, not just the first.

Always inspect the raw bytes of a string column when joins fail unexpectedly. A value that looks like 'New York' in a query result may contain a hidden character that prevents it from matching 'New York' in another table.

Handling NULL vs Empty String

A critical distinction in string cleaning is the difference between NULL and an empty string. NULL means "no value was provided." An empty string means "a value was provided, and it is empty." These are semantically different and should not be conflated.

-- Distinguish NULL from empty string
SELECT
    customer_id,
    CASE
        WHEN notes IS NULL THEN 'not_provided'
        WHEN TRIM(notes) = '' THEN 'provided_but_empty'
        ELSE 'has_content'
    END AS notes_status
FROM customers;

This distinction matters for analysis. A survey respondent who skipped a question (NULL) is different from one who intentionally left it blank (empty string). Treating both as the same loses information. In most cleaning pipelines, convert empty strings to NULL after trimming, so that "no information" is represented consistently. But make this conversion deliberately, not accidentally, and document it when you do it.

Category Normalization and Controlled Vocabularies

Categorical columns are where string normalization has the highest impact. A status column with values 'active', 'Active', 'ACTIVE', 'A', 'enabled', and '1' has six representations of two states. Any analysis on this column is meaningless until the variants are mapped to a controlled vocabulary.

Building a Mapping Table

The most maintainable approach is a mapping table that defines every variant and its canonical form. This table serves as documentation, a validation reference, and a join target for cleaning.

-- Mapping table for status normalization
CREATE TABLE status_mapping (
    raw_value VARCHAR(50) PRIMARY KEY,
    canonical_value VARCHAR(50) NOT NULL
);

INSERT INTO status_mapping (raw_value, canonical_value) VALUES
    ('active', 'active'),
    ('Active', 'active'),
    ('ACTIVE', 'active'),
    ('A', 'active'),
    ('enabled', 'active'),
    ('1', 'active'),
    ('inactive', 'inactive'),
    ('Inactive', 'inactive'),
    ('INACTIVE', 'inactive'),
    ('I', 'inactive'),
    ('disabled', 'inactive'),
    ('0', 'inactive');

-- Apply the mapping
SELECT
    c.customer_id,
    COALESCE(m.canonical_value, 'unknown') AS status_clean
FROM customers c
LEFT JOIN status_mapping m ON c.status = m.raw_value;

The COALESCE handles unmapped values by assigning 'unknown'. This is critical: if a new variant appears in the data (e.g., 'pending'), it will not match any mapping row and will become 'unknown' rather than being silently dropped or passed through in its raw form.

Validating Against the Vocabulary

Once a controlled vocabulary exists, use it to validate incoming data. Any value not in the vocabulary is either a new variant that needs a mapping or a data error.

-- Find values not in the controlled vocabulary
SELECT DISTINCT status
FROM customers
WHERE status NOT IN (SELECT raw_value FROM status_mapping);

This query returns every distinct status value that lacks a mapping. Review each one, decide its canonical form, and add it to the mapping table. This process is iterative: each new dataset introduces new variants, and the mapping table grows over time.

When to Use CASE Instead of a Mapping Table

For small, stable vocabularies (e.g., 'M'/'F' for gender), a CASE expression is simpler than a mapping table.

SELECT
    customer_id,
    CASE UPPER(gender)
        WHEN 'M' THEN 'male'
        WHEN 'F' THEN 'female'
        WHEN 'N' THEN 'non_binary'
        ELSE 'unknown'
    END AS gender_clean
FROM customers;

Use CASE when the vocabulary is small and unlikely to change. Use a mapping table when the vocabulary is large, evolving, or shared across multiple tables and pipelines. The mapping table is also preferable when non-technical stakeholders need to review and update the vocabulary without modifying code.

Hierarchical Normalization

Some categorical columns have a natural hierarchy. A location column might contain country, state, and city in a single string: 'US, CA, San Francisco'. Normalization can extract these into separate columns, each with its own controlled vocabulary.

SELECT
    SPLIT_PART(location, ',', 1) AS country,
    SPLIT_PART(location, ',', 2) AS state,
    SPLIT_PART(location, ',', 3) AS city
FROM locations;

Each extracted column can then be normalized independently: country codes standardized to ISO 3166, state names mapped to abbreviations, city names title-cased. This hierarchical approach produces cleaner data than trying to normalize the combined string as a single value.

The same principle applies to other composite strings: email addresses (local part and domain), phone numbers (country code, area code, number), and addresses (street, city, postal code). Decompose first, then normalize each component.

Date Parsing and Canonical Formats

Dates are the most complex data type to normalize because they have many valid representations and because the representation often depends on locale. '01/02/2024' is January 2nd in the US (MM/DD/YYYY) and February 1st in Europe (DD/MM/YYYY). Without knowing the locale, the string is ambiguous.

The Canonical Date Format

Choose one canonical date format and enforce it everywhere. The ISO 8601 standard (YYYY-MM-DD) is the correct choice for storage and processing because:

1. It is unambiguous: year first, then month, then day.
2. It sorts lexicographically in chronological order.
3. It is recognized by every database and programming language.
4. It has no locale-dependent interpretation.

-- Convert various date formats to ISO 8601
SELECT
    order_id,
    CASE
        -- MM/DD/YYYY (US format)
        WHEN order_date LIKE '__/__/____' THEN
            TO_DATE(order_date, 'MM/DD/YYYY')
        -- DD-MM-YYYY (European format)
        WHEN order_date LIKE '__-__-____' THEN
            TO_DATE(order_date, 'DD-MM-YYYY')
        -- YYYY-MM-DD (already canonical)
        WHEN order_date LIKE '____-__-__' THEN
            TO_DATE(order_date, 'YYYY-MM-DD')
        ELSE NULL
    END AS order_date_clean
FROM orders;

The CASE expression uses pattern matching to identify the format, then TO_DATE parses it into a proper date type. The ELSE NULL catches unparseable values so they can be investigated rather than causing the entire query to fail.

The Ambiguity Problem

'01/02/2024' matches both the MM/DD/YYYY and DD/MM/YYYY patterns. You cannot resolve this ambiguity with syntax alone. You need domain knowledge:

• If the data comes from a US system, use MM/DD/YYYY.
• If the data comes from a European system, use DD/MM/YYYY.
• If the data is mixed, you need a per-row or per-source indicator.

When the source system is known, store that information and use it to select the correct parser. When the source is unknown, flag ambiguous dates for manual review rather than guessing.

Validating Date Ranges

Once dates are parsed, validate them against plausible ranges. A birth_date of '2050-01-01' is a date but not a plausible birth date. An order_date of '1900-01-01' may be a default value, not a real order.

-- Flag implausible dates
SELECT
    order_id,
    order_date,
    CASE
        WHEN order_date > CURRENT_DATE THEN 'future_date'
        WHEN order_date < '2000-01-01' THEN 'too_old'
        WHEN order_date IS NULL THEN 'missing'
        ELSE 'valid'
    END AS date_status
FROM orders;

The thresholds (2000-01-01, CURRENT_DATE) are domain-specific. A historical dataset of 19th-century transactions would have a different lower bound. The key is to define the range explicitly and flag anything outside it.

Extracting Date Components

After normalization, extract components for analysis. Year, month, and day-of-week are the most common extractions.

SELECT
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DOW FROM order_date) AS day_of_week,
    TO_CHAR(order_date, 'Day') AS day_name
FROM orders;

These components enable time-series analysis: grouping by month, comparing year-over-year, identifying weekly patterns. They are only reliable after the underlying dates are normalized and validated.

Relative Date Handling

Some datasets contain relative dates: 'yesterday', 'last week', '2024-Q1', 'March 2024'. These must be resolved to absolute dates before they can be used in analysis.

-- Resolve relative date references
SELECT
    event_id,
    CASE
        WHEN event_date_raw = 'yesterday' THEN CURRENT_DATE - INTERVAL '1 day'
        WHEN event_date_raw = 'last_week' THEN CURRENT_DATE - INTERVAL '7 days'
        WHEN event_date_raw LIKE '%-Q1' THEN TO_DATE(SUBSTRING(event_date_raw, 1, 4) || '-01-01', 'YYYY-MM-DD')
        WHEN event_date_raw LIKE '%-Q2' THEN TO_DATE(SUBSTRING(event_date_raw, 1, 4) || '-04-01', 'YYYY-MM-DD')
        WHEN event_date_raw LIKE '%-Q3' THEN TO_DATE(SUBSTRING(event_date_raw, 1, 4) || '-07-01', 'YYYY-MM-DD')
        WHEN event_date_raw LIKE '%-Q4' THEN TO_DATE(SUBSTRING(event_date_raw, 1, 4) || '-10-01', 'YYYY-MM-DD')
        ELSE NULL
    END AS event_date_resolved
FROM events;

Relative dates are context-dependent. 'yesterday' means different things depending on when the data was collected. Always resolve relative dates at the time of processing and store the absolute date, not the relative reference. If you store 'yesterday' in the database, the value becomes wrong the next day.

Building Reusable Normalization Functions

Normalization is not a one-time operation. New data arrives daily, and it arrives with the same variants you cleaned last month. If you clean by hand each time, you will miss variants, apply inconsistent rules, and produce results that cannot be reproduced.

The solution is to package normalization logic as reusable functions that are applied consistently to every dataset.

SQL Functions for String Normalization

-- Reusable function to clean and standardize a string
CREATE OR REPLACE FUNCTION clean_string(input TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN TRIM(REGEXP_REPLACE(UPPER(input), '[\\s\\u00A0\\u200B]+', ' ', 'g'));
END;
$$ LANGUAGE plpgsql;

-- Reusable function to normalize a date with a known format
CREATE OR REPLACE FUNCTION parse_us_date(input TEXT)
RETURNS DATE AS $$
BEGIN
    RETURN TO_DATE(input, 'MM/DD/YYYY');
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

The clean_string function trims whitespace, converts to uppercase, and collapses internal whitespace to single spaces. The parse_us_date function parses a US-format date and returns NULL for unparseable values instead of raising an error.

Applying Functions in a Pipeline

Once functions exist, apply them in a transformation pipeline that processes every row consistently.

-- Normalization pipeline for a staging table
INSERT INTO clean_customers (customer_id, name, email, status, created_date)
SELECT
    customer_id,
    INITCAP(TRIM(name)) AS name,
    LOWER(TRIM(email)) AS email,
    COALESCE(m.canonical_value, 'unknown') AS status,
    parse_us_date(created_date_raw) AS created_date
FROM staging_customers s
LEFT JOIN status_mapping m ON clean_string(s.status) = m.raw_value;

This pipeline reads from a staging table, applies normalization functions, joins against the mapping table, and writes to a clean table. The staging table preserves the raw data; the clean table contains only normalized values. This separation allows you to re-run the pipeline with updated rules without re-importing the source data.

Testing Normalization Functions

Functions must be tested against known inputs and expected outputs. Create a test table with edge cases and verify the function handles each correctly.

-- Test cases for clean_string
SELECT
    input,
    clean_string(input) AS output,
    CASE clean_string(input)
        WHEN 'NEW YORK' THEN 'PASS'
        ELSE 'FAIL'
    END AS test_result
FROM (VALUES
    ('  new york  '),
    ('NEW\u00A0YORK'),
    ('new\tyork'),
    ('')
) AS tests(input);

Test cases should cover: leading/trailing whitespace, internal whitespace, mixed casing, empty strings, and NULL values. A function that passes all test cases is ready for production use.

Versioning Normalization Logic

Normalization rules change. A new status value appears. A date format is discovered. When rules change, the change must be versioned so that historical analyses remain reproducible.

Store normalization logic in version-controlled code (Git), not in ad-hoc queries. Tag each version with a date and a description of what changed. When you re-run an analysis, use the version of the normalization logic that was current at the time of the original analysis.

This is the difference between a one-off cleaning script and a maintainable data pipeline. The script produces a result; the pipeline produces a reproducible, auditable, and updatable result.

Idempotency in Normalization

A normalization function should be idempotent: applying it twice produces the same result as applying it once. This property is essential for pipelines that may reprocess data.

-- Idempotent: clean_string(clean_string(x)) = clean_string(x)
SELECT clean_string(city) AS once, clean_string(clean_string(city)) AS twice
FROM locations;
-- Both columns are identical

If a function is not idempotent, re-running the pipeline can corrupt already-clean data. For example, a function that removes the first character is not idempotent: applied once to 'New York' it produces 'ew York'; applied twice it produces 'w York'. Design normalization functions so that their output is always in the canonical form, and applying the function to canonical form is a no-op.

Test idempotency explicitly: run the function on already-clean data and verify the output is unchanged. This test catches subtle bugs that only appear on reprocessing.