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.

What is the question ? How to store it, How to search for it ? How to design the database for it ? — Rohit Gupta
– Rohit Gupta, Commented Jan 5, 2023 at 12:10
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community
– Community Bot, Commented Jan 5, 2023 at 12:10

Daniel Vérité · Accepted Answer · 2023-01-05 13:51:19Z

𝐋𝐈𝐅𝐄 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.

Absolutely fantastic answer and background information - thank you so much! Keep being awesome. — unomi
– unomi, Commented Jan 5, 2023 at 20:37

Stack Exchange Network

postgresql matching or converting utf-8 variant strings

1 Answer 1

Explicit normalization approach

Collation approach

Your Answer

Hot Network Questions

postgresql matching or converting utf-8 variant strings

1 Answer 1

Explicit normalization approach

Collation approach

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions