Skip to main content
CodeSparring

String Functions for Cleaning

Level 1: Level 1: SQL Foundations (Reading Source Data)medium14 minLOWER/UPPERTRIMSUBSTRREPLACELENGTHINSTR

Trim, case-fold, and slice text to standardize messy source strings.

Standardize before you join

Joins and dedup only work when keys match exactly. ' Ana@Example.com ' and 'ana@example.com' are different strings: a join on them fails, a dedup keeps both. Before any join, a staging model normalizes the key: trim whitespace, lowercase, strip prefixes. Getting this right is the difference between a clean dimension and a duplicated one.

In the warehouse this differs. SQLite's TRIM(s, chars) strips a set of characters; the ANSI form other engines use is TRIM(BOTH chars FROM s). And Postgres, Snowflake, and BigQuery ship REGEXP_REPLACE for pattern-based cleanup, which SQLite does not, so heavier key normalization there is one function call instead of nested REPLACEs.

The toolkit

  • LOWER(s) / UPPER(s): case-fold.
  • TRIM(s): remove leading/trailing whitespace (LTRIM/RTRIM for one side; TRIM(s, chars) to trim specific characters).
  • SUBSTR(s, start, len): slice (1-indexed in SQLite).
  • REPLACE(s, from, to): swap all occurrences.
  • LENGTH(s): character count. INSTR(s, sub): 1-based position of sub (0 if absent).

Worked example (a cleaned email key):

SELECT
  customer_id,
  LOWER(TRIM(email)) AS email_key
FROM customers_raw;

Anatomy.

LOWER( TRIM( email ) )
       └ strip spaces ┘
└─ then case-fold ─┘
SUBSTR('SKU-AUD-01', 5)      -> 'AUD-01'   (from position 5 to end)
REPLACE('US-A', 'US-', '')   -> 'A'

Pitfall. SUBSTR is 1-indexed in SQLite (and Oracle), but some languages/dialects are 0-indexed. Count carefully. TRIM only removes whitespace by default, not interior spaces ('a b' stays 'a b'); use REPLACE(s, ' ', '') to strip all spaces. And functions nest inside-out: LOWER(TRIM(x)) trims first, then lowercases.

Recap. TRIM + LOWER build matchable join keys; SUBSTR/REPLACE/INSTR slice and rewrite messy source text. Standardize keys before any join or dedup.

Sample data for this example
CREATE TABLE customers_raw (
  customer_id INTEGER,
  email       TEXT
);
INSERT INTO customers_raw VALUES
  (1, '  Ana@Example.com '),
  (2, 'LEE@example.COM'),
  (3, 'kim@Example.com  ');
Worked example (SQL)
SELECT customer_id, LOWER(TRIM(email)) AS email_key FROM customers_raw;

Apply

Your turn

The task this lesson builds to.

Normalize each email to a trimmed, lowercase join key. Return customer_id and email_key = LOWER(TRIM(email)).

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

A staging model has to clean its join keys before anything downstream can join on them. Write a query that returns customer_id, email_key, sku_clean, and country_code_norm from customers_raw. The three derived columns are:

  • email_key: trimmed and lowercased email,
  • sku_clean: the sku with the leading 'PRD-' prefix removed (e.g. PRD-AUD-01 → AUD-01),
  • country_code_norm: the country_code trimmed and uppercased (e.g. ' us ' → US).

3 hints and 1 automated check are waiting in the workspace.