postgresql matching or converting utf-8 variant strings

Question

Postgres 13

I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie 𝐋𝐈𝐅𝐄 vs life ) within postgresql.

I am running into issues matching variant characters, consider

-- This works as expected
select 'life' ilike '%life%' matches 

-- I would like to also be able to match against this source text like this
select '𝐋𝐈𝐅𝐄' ilike '%life%' matches

Note that there are myriad variants, I do not particularly care about matching against non-ascii representable characters at this point, that is I think I would be fine with a lossy conversion from utf-8 to ascii as long as 𝐋𝐈𝐅𝐄 would end up matching with life.

Answer 1

𝐋𝐈𝐅𝐄 vs LIFE is a case of Unicode "compatibility equivalence", which is defined in UAX#15 as:

Compatibility equivalence is a weaker type of equivalence between characters or sequences of characters which represent the same abstract character (or sequence of abstract characters), but which may have distinct visual appearances or behaviors. The visual appearances of the compatibility equivalent forms typically constitute a subset of the expected range of visual appearances of the character (or sequence of characters) they are equivalent to

Explicit normalization approach

To normalize for compatibility equivalence testing, the NFKC or NFKD forms can be used.

Postgres 13 or newer provides the normalize function for that:

=> select normalize('𝐋𝐈𝐅𝐄', nfkc);
 normalize 
-----------
 LIFE

Comparing the lowercase life to the uppercase LIFE happens at a different level, which is of course more widely known. ilike may be used as in the question, or both strings can be converted to the same case with lower or upper.

Collation approach

Another approach is to use a non-deterministic ICU collation (Postgres 12 or newer) that compares at the "secondary level". At this level, the compatibility equivalence and the case equivalence are taken into account directly by the collation:

=> CREATE COLLATION nd2 (
  provider = 'icu',
  locale = '@colStrength=secondary', -- or 'und-u-ks-level2'
  deterministic = false
);

=> select 'life' = '𝐋𝐈𝐅𝐄' collate "nd2";
 ?column? 
----------
 t

Such collations cannot be used with the LIKE operator, though.