Data Cleaning Patterns

Normalize Text

Trim and Lowercase

Cleaning text often means removing extra whitespace and using a consistent case before comparison.

Program

Play the script to choose an email domain and return normalized matching addresses.

domain_filter
normalize_email_text.sql
Replay: real traced execution (multi-file project)
CREATE TABLE raw_contacts (id INTEGER, email TEXT);
INSERT INTO raw_contacts VALUES (1, ' ADA@Example.com '), (2, 'lin@work.com'), (3, ' NIA@example.COM');
WITH params(domain_filter) AS (VALUES ('example.com')) SELECT id, lower(trim(email)) AS normalized_email FROM raw_contacts WHERE lower(trim(email)) LIKE '%' || (SELECT domain_filter FROM params) ORDER BY id;

CREATE TABLE raw_contacts (id INTEGER, email TEXT);
INSERT INTO raw_contacts VALUES (1, ' ADA@Example.com '), (2, 'lin@work.com'), (3, ' NIA@example.COM');
WITH params(domain_filter) AS (VALUES ('work.com')) SELECT id, lower(trim(email)) AS normalized_email FROM raw_contacts WHERE lower(trim(email)) LIKE '%' || (SELECT domain_filter FROM params) ORDER BY id;

  1. tables ← 1 row

    1CREATE TABLE raw_contacts (id INTEGER, email TEXT);2INSERT INTO raw_contacts VALUES (1, ' ADA@Example.com '), (2, 'lin@work.com'), (3, ' NIA@example.COM');
    values this step1 rowtables

  2. raw_contacts ← 3 rows

    1CREATE TABLE raw_contacts (id INTEGER, email TEXT);2INSERT INTO raw_contacts VALUES (1, ' ADA@Example.com '), (2, 'lin@work.com'), (3, ' NIA@example.COM');3WITH params(domain_filter) AS (VALUES ('example.com')) SELECT id, lower(trim(email)) AS normalized_email FROM raw_contacts WHERE lower(trim(email)) LIKE '%' || (SELECT domain_filter FROM params) ORDER BY id;
    values this step3 rowsraw_contacts

  3. result ← 2 rows

    2INSERT INTO raw_contacts VALUES (1, ' ADA@Example.com '), (2, 'lin@work.com'), (3, ' NIA@example.COM');3WITH params(domain_filter) AS (VALUES ('example.com')) SELECT id, lower(trim(email)) AS normalized_email FROM raw_contacts WHERE lower(trim(email)) LIKE '%' || (SELECT domain_filter FROM params) ORDER BY id;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE raw_contacts (id INTEGER, email TEXT);2INSERT INTO raw_contacts VALUES (1, ' ADA@Example.com '), (2, 'lin@work.com'), (3, ' NIA@example.COM');
    values this step1 rowtables

  2. raw_contacts ← 3 rows

    1CREATE TABLE raw_contacts (id INTEGER, email TEXT);2INSERT INTO raw_contacts VALUES (1, ' ADA@Example.com '), (2, 'lin@work.com'), (3, ' NIA@example.COM');3WITH params(domain_filter) AS (VALUES ('work.com')) SELECT id, lower(trim(email)) AS normalized_email FROM raw_contacts WHERE lower(trim(email)) LIKE '%' || (SELECT domain_filter FROM params) ORDER BY id;
    values this step3 rowsraw_contacts

  3. result ← 1 row

    2INSERT INTO raw_contacts VALUES (1, ' ADA@Example.com '), (2, 'lin@work.com'), (3, ' NIA@example.COM');3WITH params(domain_filter) AS (VALUES ('work.com')) SELECT id, lower(trim(email)) AS normalized_email FROM raw_contacts WHERE lower(trim(email)) LIKE '%' || (SELECT domain_filter FROM params) ORDER BY id;
    values this step1 rowresult
trim `trim(email)` removes leading and trailing spaces.
lowercase `lower(...)` gives text a consistent comparison form.
cleaning filter The selector changes the normalized domain filter without changing the cleaning expression.