在 postgreSQL 中删除重音的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13596638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Function to remove accents in postgreSQL
提问by chamini2
Possible Duplicate:
Does PostgreSQL support “accent insensitive” collations?
I'm trying to remove the accents so when I make a SELECTit ignores them.
我正在尝试删除重音，因此当我进行SELECT 时它会忽略它们。
Example:
例子：
SELECT * FROM table WHERE
table.id ILIKE 'Jose';
It returns:
它返回：
José
Jose
Jósé
Jóse
or something like that.
或类似的东西。
I found these functions and they wont work, I'm thinking it may be the fact that I'm using Struts 1.X, please check them out and tell me where I'm wrong or what other function should I use.
我找到了这些函数，但它们不起作用，我想这可能是因为我使用的是 Struts 1.X，请检查它们并告诉我我错在哪里或我应该使用什么其他函数。
FIRST FUNCTION
第一个功能
CREATE OR REPLACE FUNCTION unaccent_string(text) RETURNS text AS $$
DECLARE
input_string text := ;
BEGIN
input_string := translate(input_string, 'a???ā??á????ā??', 'aaaaaaaaaaaaaaa');
input_string := translate(input_string, 'èééê?ē???ěē???ě', 'eeeeeeeeeeeeeee');
input_string := translate(input_string, 'ìí??ì?ī?ìí??ì?ī?', 'iiiiiiiiiiiiiiii');
input_string := translate(input_string, 'ó???ō??òó???ō??', 'ooooooooooooooo');
input_string := translate(input_string, 'ùú?ü?ū??ùú?ü?ū??', 'uuuuuuuuuuuuuuuu');
return input_string;
END;
$$ LANGUAGE plpgql;
SECOND FUNCTION
第二功能
CREATE OR REPLACE FUNCTION norm_text_latin(character varying)
RETURNS character varying AS
$BODY$
declare
p_str alias for ;
v_str varchar;
begin
select translate(p_str, 'àá????', 'AAAAAA') into v_str;
select translate(v_str, 'éè?ê', 'EEEE') into v_str;
select translate(v_str, 'ìí??', 'IIII') into v_str;
select translate(v_str, 'ìí??', 'IIII') into v_str;
select translate(v_str, 'òó???', 'OOOOO') into v_str;
select translate(v_str, 'ùú?ü', 'UUUU') into v_str;
select translate(v_str, 'àáa???', 'aaaaaa') into v_str;
select translate(v_str, 'èéê?', 'eeee') into v_str;
select translate(v_str, 'ìí??', 'iiii') into v_str;
select translate(v_str, 'òó???', 'ooooo') into v_str;
select translate(v_str, 'ùú?ü', 'uuuu') into v_str;
select translate(v_str, '??', 'Cc') into v_str;
return v_str;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Both functions generate the following error when I run the file in NetBeans:
当我在 NetBeans 中运行文件时，这两个函数都会生成以下错误：
ERROR: unterminated dollar-quoted string at or near "*the string that starts the function*"
回答by titanofold
Use the unaccentmodule that ships with PostgreSQL.
使用PostgreSQL 附带的unaccent模块。
somedb=# CREATE EXTENSION unaccent;
CREATE EXTENSION
somedb=# SELECT unaccent('H?tel');
unaccent
----------
Hotel
somedb=# SELECT * FROM table WHERE lower(unaccent(table.id)) = lower('Jose');
And speed things up a bit by generating an accentless, lower-cased index:
并通过生成一个无重音的小写索引来加快速度：
somedb=# CREATE INDEX CONCURRENTLY ON table (lower(unaccent(id)));
