有多字节感知的Postgresql Levenshtein吗？ [英] Is there a multibyte-aware Postgresql Levenshtein?

问题描述

当我将 fuzzystrmatch levenshtein 函数与变音符号一起使用时，它会返回错误的/忽略多字节的结果：

When I use the fuzzystrmatch levenshtein function with diacritic characters it returns a wrong / multibyte-ignorant result:

select levenshtein('ą', 'x');
levenshtein 
-------------
       2

（注意：第一个字符是下面带有变音符号的'a'，在我将其复制到此处后无法正确显示）

(Note: the first character is an 'a' with a diacritic below, it is not rendered properly after I copied it here)

strong> fuzzystrmatch 文档（ https://www.postgresql.org/docs /9.1/fuzzystrmatch.html ）警告：

The fuzzystrmatch documentation (https://www.postgresql.org/docs/9.1/fuzzystrmatch.html) warns that:

目前，soundex，metaphone，dmetaphone和dmetaphone_alt函数确实可以不适用于多字节编码（例如UTF-8）。

At present, the soundex, metaphone, dmetaphone, and dmetaphone_alt functions do not work well with multibyte encodings (such as UTF-8).

但是它没有命名 levenshtein strong>功能，我想知道是否有 levenshtein 的多字节感知版本。

But as it does not name the levenshtein function, I was wondering if there is a multibyte aware version of levenshtein.

我知道我可以使用 unaccent 作为解决方法，但我需要保留变音符号。

I know that I could use unaccent function as a workaround but I need to keep the diacritics.

推荐答案

注意：所以@Nick Barnes在他的 answer 中建议对相关问题。

带变音符号的'a'是字符序列，即a a 和组合字符的变音符号̨： E'a\u0328'

The 'a' with a diacritic is a character sequence, i.e. a combination of a and a combining character, the diacritic ̨ : E'a\u0328'

有一个等效的预组合字符±： E'\u0105'

There is an equivalent precomposed character ą: E'\u0105'

一种解决方案是规范化 Unicode字符串，即转换组合字符

A solution would be to normalise the Unicode strings, i.e. to convert the combining character sequence into the precomposed character before comparing them.

不幸的是，Postgres似乎没有内置的Unicode规范化功能，但是您可以通过< a href = https://www.postgresql.org/docs/current/plperl.html rel = nofollow noreferrer> PL / Perl 或 PL / Python 语言扩展。

Unfortunately, Postgres doesn't seem to have a built-in Unicode normalisation function, but you can easily access one via the PL/Perl or PL/Python language extensions.

例如：

create extension plpythonu;

create or replace function unicode_normalize(str text) returns text as $$
  import unicodedata
  return unicodedata.normalize('NFC', str.decode('UTF-8'))
$$ language plpythonu;

现在，作为字符序列 E'a\u0328' unicode_normalize （levenshtein），将code>映射到等效的预组合字符 E'\u0105'距离是正确的：

Now, as the character sequence E'a\u0328' is mapped onto the equivalent precomposed character E'\u0105' by using unicode_normalize, the levenshtein distance is correct:

select levenshtein(unicode_normalize(E'a\u0328'), 'x');
levenshtein
-------------
           1

这篇关于有多字节感知的Postgresql Levenshtein吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！