SQL Text Functions: Clean Labels Without Changing Their Meaning

Text that looks identical can behave differently in a filter. An extra space or inconsistent capitalisation can split a category into several groups. SQL text functions help inspect those differences, but cleaning should follow the meaning of the field rather than blindly rewriting everything.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Keep raw and cleaned values side by side

WITH labels(raw_label) AS (
  VALUES (' Nairobi '), ('NAIROBI'), ('Mombasa')
)
SELECT raw_label,
       LENGTH(raw_label) AS raw_length,
       LOWER(TRIM(raw_label)) AS clean_label
FROM labels;

The first raw length is 9 because Nairobi has seven letters plus two spaces. LOWER converts letters to lowercase, and SQLite's default TRIM removes surrounding spaces. The clean values are nairobi, nairobi and mombasa.

The raw column remains available for comparison. This SELECT does not update a stored table. That separation lets you test whether a proposed cleaning rule is appropriate before applying it to real data.

Understand what TRIM does not do

Default TRIM does not remove every possible invisible character or repair spaces inside a value. A non-breaking space from a copied webpage can remain. Two spaces inside a person's name may also require a different rule from two spaces inside a free-text note.

Do not remove all spaces simply because a join fails. A label such as New York needs its internal space. For identifiers, punctuation and leading zeros can be meaningful. The correct transformation depends on the field definition.

Group by a normalised expression

WITH labels(raw_label) AS (
  VALUES (' Nairobi '), ('NAIROBI'), ('Mombasa')
)
SELECT LOWER(TRIM(raw_label)) AS clean_label,
       COUNT(*) AS records
FROM labels
GROUP BY LOWER(TRIM(raw_label))
ORDER BY clean_label;

The summary is mombasa 1 and nairobi 2. The group count still totals three input records. Normalising a label changes grouping behaviour; it does not prove that two records describe the same customer or event.

Extract part of a known format

SELECT SUBSTR('ORD-104',5,3) AS numeric_part; returns the text 104. SQLite positions start at 1, so character 5 is the first digit. This works because the example format is known. It is fragile if some IDs have a longer prefix or more digits.

Use a proper stored field when possible instead of repeatedly guessing information from a display label. Also remember that lowercase behaviour for non-ASCII text depends on database support and extensions. Do not assume the simple English examples establish correct language-aware cleaning for every script.

Try it yourself

Create a CTE containing ' Tech ', 'TECH' and 'Home'. Return the cleaned label and its count. Predict the result, then explain why this is not enough to deduplicate customer records.

WITH labels(raw_label) AS (
  VALUES (' Tech '), ('TECH'), ('Home')
)
SELECT LOWER(TRIM(raw_label)) AS clean_label,
       COUNT(*) AS records
FROM labels
GROUP BY LOWER(TRIM(raw_label))
ORDER BY clean_label;

Keep exploring: SQL learning path.