{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"strings-trim-case-and-unicode","title":"Text that looks identical and is not","url":"https://addaly.com/learn/data-and-sql/strings-trim-case-and-unicode","position":48,"of":66,"minutes":9,"answer":"Two strings that look identical can differ by invisible whitespace, case, or the Unicode form of an accent; build a normalised key column for grouping and joining and keep the original for display.","keyIdea":"Two strings that look identical can differ by invisible whitespace, case, or the Unicode form of an accent; build a normalised key column for grouping and joining and keep the original for display."},"body":{"format":"markdown","text":"## The GROUP BY that shows the same city twice\n\n```sql\nSELECT city, count(*) FROM raw_payments GROUP BY city ORDER BY city;\n-- Lagos      4,120\n-- Lagos      2,893\n```\n\nTwo 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.\n\n## Whitespace that trim() does not remove\n\n`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.\n\n```sql\nSELECT city, length(city), encode(city::bytea, 'hex')\nFROM raw_payments\nGROUP BY city;\n```\n\n`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:\n\n```sql\nbtrim(translate(city, chr(160) || chr(8203) || chr(9), '   '))\n```\n\n`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.\n\n## Case\n\n`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.\n\n## One accent, two spellings\n\n`é` 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.\n\nUnicode 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.\n\n## Build a key, keep the original\n\nThe pattern that comes out of all this is two columns per messy text field:\n\n```sql\ncity                                                       AS city_display,\nlower(btrim(unaccent(normalize(\n  translate(city, chr(160) || chr(8203) || chr(9), '   '), NFC)))) AS city_key\n```\n\nGroup, 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.\n\n## Pulling structure out of text\n\nA 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.\n\n## What matching cannot fix\n\nAfter 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."},"check":{"question":"`GROUP BY customer_name` shows `Renée Dubois` twice with separate counts, and `length()` reports 12 for one and 13 for the other. Which explanation fits and what fixes it?","options":[{"text":"One row has a trailing space, and `trim()` in the grouping key will merge them.","correct":false,"tempting":"A trailing space would show the same one-character difference, but it is not the only cause and the clue points elsewhere; trim() also leaves non-breaking spaces and combining characters alone."},{"text":"One row stores `é` as one code point and the other as `e` plus a combining accent; `normalize(name, NFC)` in the key merges them.","correct":true},{"text":"The two rows have different letter case that the terminal is hiding, and `lower()` in the grouping key will merge them into a single row.","correct":false,"tempting":"Case differences do not change a string's length; both spellings would report 12."},{"text":"One value is stored in Windows-1252 and the other in UTF-8, and re-encoding the table to UTF-8 will merge them.","correct":false,"tempting":"A Postgres text column has one encoding for the whole database; a mixed-encoding value would show as mangled characters, not as a clean one-character length difference."}]},"prev":{"slug":"dates-in-five-formats","title":"Parsing dates that were typed by people","url":"https://addaly.com/learn/data-and-sql/dates-in-five-formats"},"next":{"slug":"duplicates-and-which-to-keep","title":"Duplicates: finding them, and deciding which row survives","url":"https://addaly.com/learn/data-and-sql/duplicates-and-which-to-keep"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Text that looks identical and is not', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/strings-trim-case-and-unicode (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Text that looks identical and is not","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Two strings that look identical can differ by invisible whitespace, case, or the Unicode form of an accent; build a normalised key column for grouping and joining and keep the original for display.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-group-by-that-shows-the-same-city-twice","runs":[{"text":"The GROUP BY that shows the same city twice"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT city, count(*) FROM raw_payments GROUP BY city ORDER BY city;\n-- Lagos      4,120\n-- Lagos      2,893"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"whitespace-that-trim-does-not-remove","runs":[{"text":"Whitespace that trim() does not remove"}]},{"t":"p","runs":[{"text":"trim()","code":true},{"text":" 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."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT city, length(city), encode(city::bytea, 'hex')\nFROM raw_payments\nGROUP BY city;"},{"t":"p","runs":[{"text":"length","code":true},{"text":" exposes the difference: one "},{"text":"Lagos","code":true},{"text":" is five characters and the other is six. "},{"text":"encode(..., 'hex')","code":true},{"text":" shows the bytes, and "},{"text":"c2a0","code":true},{"text":" at the end is the non-breaking space. Remove them explicitly:"}]},{"t":"code","lang":"sql","label":"SQL","text":"btrim(translate(city, chr(160) || chr(8203) || chr(9), '   '))"},{"t":"p","runs":[{"text":"translate","code":true},{"text":" maps each listed character to a space, and "},{"text":"btrim","code":true},{"text":" then removes the spaces. "},{"text":"regexp_replace(city, '\\s+', ' ', 'g')","code":true},{"text":" collapses runs of internal whitespace to one, so "},{"text":"Lagos  State","code":true},{"text":" and "},{"text":"Lagos State","code":true},{"text":" agree."}]},{"t":"h2","id":"case","runs":[{"text":"Case"}]},{"t":"p","runs":[{"text":"lower()","code":true},{"text":" makes "},{"text":"LAGOS","code":true},{"text":", "},{"text":"Lagos","code":true},{"text":" and "},{"text":"lagos","code":true},{"text":" equal. It is not always safe: the Turkish dotless i and the German "},{"text":"ß","code":true},{"text":" 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 "},{"text":"lower()","code":true},{"text":" is fine, and the display column keeps the original."}]},{"t":"h2","id":"one-accent-two-spellings","runs":[{"text":"One accent, two spellings"}]},{"t":"p","runs":[{"text":"é","code":true},{"text":" can be stored as a single code point, U+00E9, or as the letter "},{"text":"e","code":true},{"text":" followed by a combining acute accent, U+0065 U+0301. They render identically. They are different strings, of different lengths, and "},{"text":"=","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"Unicode defines a normal form so that equal-looking text has one representation. Postgres 13 and later has "},{"text":"normalize(name, NFC)","code":true},{"text":"; DuckDB has "},{"text":"nfc_normalize(name)","code":true},{"text":". 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 "},{"text":"unaccent","code":true},{"text":" extension in Postgres turns "},{"text":"Renée","code":true},{"text":" into "},{"text":"Renee","code":true},{"text":", and the matching key uses that while the display column keeps the accent."}]},{"t":"h2","id":"build-a-key-keep-the-original","runs":[{"text":"Build a key, keep the original"}]},{"t":"p","runs":[{"text":"The pattern that comes out of all this is two columns per messy text field:"}]},{"t":"code","lang":"sql","label":"SQL","text":"city                                                       AS city_display,\nlower(btrim(unaccent(normalize(\n  translate(city, chr(160) || chr(8203) || chr(9), '   '), NFC)))) AS city_key"},{"t":"p","runs":[{"text":"Group, join and filter on "},{"text":"city_key","code":true},{"text":". Show "},{"text":"city_display","code":true},{"text":". Never overwrite the original, because the day you need to know that the source wrote "},{"text":"Lagos, NG","code":true},{"text":" is the day after you deleted it."}]},{"t":"h2","id":"pulling-structure-out-of-text","runs":[{"text":"Pulling structure out of text"}]},{"t":"p","runs":[{"text":"A phone column with "},{"text":"+234 803 123 4567","code":true},{"text":", "},{"text":"08031234567","code":true},{"text":" and "},{"text":"803-123-4567 (home)","code":true},{"text":" has one useful thing in it, the digits. "},{"text":"regexp_replace(phone, '[^0-9]', '', 'g')","code":true},{"text":" strips everything else, and a "},{"text":"CASE","code":true},{"text":" on "},{"text":"length()","code":true},{"text":" and the leading digits decides whether a country code is present. "},{"text":"substring(notes from '[A-Z]{2}[0-9]{6}')","code":true},{"text":" 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."}]},{"t":"h2","id":"what-matching-cannot-fix","runs":[{"text":"What matching cannot fix"}]},{"t":"p","runs":[{"text":"After all of the above, "},{"text":"Lagos State","code":true},{"text":" and "},{"text":"Lagos","code":true},{"text":" still differ, and so do "},{"text":"Bombay","code":true},{"text":" and "},{"text":"Mumbai","code":true},{"text":". 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."}]}],"check":{"question":[{"text":"GROUP BY customer_name","code":true},{"text":" shows "},{"text":"Renée Dubois","code":true},{"text":" twice with separate counts, and "},{"text":"length()","code":true},{"text":" reports 12 for one and 13 for the other. Which explanation fits and what fixes it?"}],"options":[{"text":[{"text":"One row has a trailing space, and "},{"text":"trim()","code":true},{"text":" in the grouping key will merge them."}],"correct":false,"tempting":[{"text":"A trailing space would show the same one-character difference, but it is not the only cause and the clue points elsewhere; trim() also leaves non-breaking spaces and combining characters alone."}]},{"text":[{"text":"One row stores "},{"text":"é","code":true},{"text":" as one code point and the other as "},{"text":"e","code":true},{"text":" plus a combining accent; "},{"text":"normalize(name, NFC)","code":true},{"text":" in the key merges them."}],"correct":true,"tempting":null},{"text":[{"text":"The two rows have different letter case that the terminal is hiding, and "},{"text":"lower()","code":true},{"text":" in the grouping key will merge them into a single row."}],"correct":false,"tempting":[{"text":"Case differences do not change a string’s length; both spellings would report 12."}]},{"text":[{"text":"One value is stored in Windows-1252 and the other in UTF-8, and re-encoding the table to UTF-8 will merge them."}],"correct":false,"tempting":[{"text":"A Postgres text column has one encoding for the whole database; a mixed-encoding value would show as mangled characters, not as a clean one-character length difference."}]}],"order":[3,1,0,2],"why":null},"prev":{"slug":"dates-in-five-formats","title":"Parsing dates that were typed by people","minutes":9},"next":{"slug":"duplicates-and-which-to-keep","title":"Duplicates: finding them, and deciding which row survives","minutes":8}}}