Text that looks identical and is not

The GROUP BY that shows the same city twice

SELECT city, count(*) FROM raw_payments GROUP BY city ORDER BY city;
-- Lagos      4,120
-- Lagos      2,893

Two rows, identical on screen. They differ in a character you cannot see. This lesson is the short list of ways two strings look the same and compare unequal, and the one-line fix for each.

Whitespace that trim() does not remove

trim() removes ordinary spaces from both ends. It does not remove a tab, a non-breaking space (the character a web page uses to stop a line wrapping, code point 160), or a zero-width space (code point 8203, which copy-and-paste from some apps inserts). All three are invisible in every tool you will look at the data with.

SELECT city, length(city), encode(city::bytea, 'hex')
FROM raw_payments
GROUP BY city;

length exposes the difference: one Lagos is five characters and the other is six. encode(..., 'hex') shows the bytes, and c2a0 at the end is the non-breaking space. Remove them explicitly:

btrim(translate(city, chr(160) || chr(8203) || chr(9), '   '))

translate maps each listed character to a space, and btrim then removes the spaces. regexp_replace(city, '\s+', ' ', 'g') collapses runs of internal whitespace to one, so Lagos State and Lagos State agree.

Case

lower() makes LAGOS, Lagos and lagos equal. It is not always safe: the Turkish dotless i and the German ß both misbehave under naive lowering, and a name-matching system for Turkish customers needs the locale-aware version. For a city key in most datasets lower() is fine, and the display column keeps the original.

One accent, two spellings

é can be stored as a single code point, U+00E9, or as the letter e followed by a combining acute accent, U+0065 U+0301. They render identically. They are different strings, of different lengths, and = says so. Files from macOS often carry the second form; files from Windows the first; a database with both has customers who cannot be found by their own name.

Unicode defines a normal form so that equal-looking text has one representation. Postgres 13 and later has normalize(name, NFC); DuckDB has nfc_normalize(name). Apply it to every text column in the clean view, once, and the problem disappears for everything downstream. Where accents should not matter at all for matching, the unaccent extension in Postgres turns Renée into Renee, and the matching key uses that while the display column keeps the accent.

Build a key, keep the original

The pattern that comes out of all this is two columns per messy text field:

city                                                       AS city_display,
lower(btrim(unaccent(normalize(
  translate(city, chr(160) || chr(8203) || chr(9), '   '), NFC)))) AS city_key

Group, join and filter on city_key. Show city_display. Never overwrite the original, because the day you need to know that the source wrote Lagos, NG is the day after you deleted it.

Pulling structure out of text

A phone column with +234 803 123 4567, 08031234567 and 803-123-4567 (home) has one useful thing in it, the digits. regexp_replace(phone, '[^0-9]', '', 'g') strips everything else, and a CASE on length() and the leading digits decides whether a country code is present. substring(notes from '[A-Z]{2}[0-9]{6}') pulls a reference number out of free text. Regular expressions are the tool for this, they are the same in Postgres and DuckDB for anything simple, and the honest limit is that they match shapes, not meanings: a ten-digit number in a notes column might be a phone, an order, or a date written without separators.

What matching cannot fix

After all of the above, Lagos State and Lagos still differ, and so do Bombay and Mumbai. Those are not encoding problems; they are the same place under two names, and no function knows that. The categories lesson later in this module handles them with a mapping table, which is a list of facts, not a transformation. Keep the two kinds of problem apart: normalisation makes identical things compare equal, and mapping declares that different things are the same.