postgresql - 在 PostgreSQL 中比较字符串

Question

在 PostgreSQL 中有什么方法可以将 UTF-8 字符转换为“相似的”ASCII 字符吗？

字符串 glāžšķūņu rūķīši 必须转换为 glazskunu rukisi。 UTF-8 文本不是某种特定语言，它可能是拉脱维亚语、俄语、英语、意大利语或任何其他语言。

这是在 where 子句中使用所必需的，因此它可能只是“比较字符串”而不是“转换字符串”。

我尝试使用 convert，但它没有给出预期的结果(例如，select convert('Ā', 'utf8', 'sql_ascii') 给出了 \304\200，不是 A)。

数据库是用以下方法创建的:

ENCODING = 'UTF8'
LC_COLLATE = 'Latvian_Latvia.1257'
LC_CTYPE = 'Latvian_Latvia.1257'

如有必要，可以更改这些参数。

Answer 1

我在 PostgreSQL Wiki 上找到了不同的方法来做到这一点.

在 plperl 中:

CREATE OR REPLACE FUNCTION unaccent_string(text) RETURNS text AS $$
my ($input_string) = @_;
$input_string =~ s/[âãäåāăą]/a;
$input_string =~ s/[ÁÂÃÄÅĀĂĄ]/A;
$input_string =~ s/[èééêëēĕėęě]/e;
$input_string =~ s/[ĒĔĖĘĚ]/E;
$input_string =~ s/[ìíîïìĩīĭ]/i;
$input_string =~ s/[ÌÍÎÏÌĨĪĬ]/I;
$input_string =~ s/[óôõöōŏő]/o;
$input_string =~ s/[ÒÓÔÕÖŌŎŐ]/O;
$input_string =~ s/[ùúûüũūŭů]/u;
$input_string =~ s/[ÙÚÛÜŨŪŬŮ]/U;
return $input_string;
$$ LANGUAGE plperl;

在纯 SQL 中:

CREATE OR REPLACE FUNCTION unaccent_string(text)
RETURNS text
IMMUTABLE
STRICT
LANGUAGE SQL
AS $$
SELECT translate(
    $1,
    'âãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
    'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
);
$$;

在 plpython 中:

create or replace function unaccent(text) returns text language plpythonu as $$
import unicodedata
rv = plpy.execute("select setting from pg_settings where name = 'server_encoding'");
encoding = rv[0]["setting"]
s = args[0].decode(encoding)
s = unicodedata.normalize("NFKD", s)
s = ''.join(c for c in s if ord(c) < 127)
return s
$$;

在您的情况下，一个 translate() 调用包含您可以在 UTF-8 table 中找到的所有字符应该足够了。