Write a query to audit every contacts.full_name, returning its contact_id and these values:
01chars: Number of Unicode code points.
02bytes: Number of bytes in the stored value.
03graphemes: Number of visible character clusters.
04needs_trim: Whether removing spaces, tabs, carriage returns and newlines from both ends changes the value.
05needs_nfc: Whether NFC normalisation changes the value, for example by combining a letter and its combining accent.
06match_key: NFC-normalise the name, replace every non-breaking space (U+00A0) with an ordinary space, collapse whitespace runs to one space, trim the ends, and lowercase the result.
Treat non-breaking spaces explicitly: they are not removed by the stated boundary trim, the whitespace regular expression or NFC normalisation.
Ordering: Sort by contact_id in ascending order.
Returncontact_idcharsbytesgraphemesneeds_trimneeds_nfcmatch_key
About this problem
You practise looking inside a text column instead of at it. Two systems load contacts, and the names they produce print identically but refuse to join, so you report the code points, bytes and visible characters of each value, flag the two different reasons a value is unclean, and build the normalised key you could actually join on. The skill is recognising that “these look the same” is a claim about rendering, not about storage, and that each kind of sameness needs its own function.
The sample holds three different problems that all look like one. An accent can be stored as a single character or as a letter plus a combining mark, so 'Carla Muñoz' = 'Carla Muñoz' is false even though the two strings are indistinguishable on screen; lower() and trim() do nothing for it. Invisible characters at the ends need trimming, but the usual trim() is not enough on its own: DuckDB’s default leaves a tab, so the flag for contact 6 comes back false. The third is the quietest. A non-breaking space survives a trim given the ordinary whitespace characters, survives NFC, and is not matched by \s in the regular-expression engine DuckDB uses, so a key that collapses whitespace will leave contact 7 standing on its own no matter how carefully it is written – which is why the prompt names it, and why the only thing that removes it is replacing it on purpose.
Read the lesson: String Functions (P12) →