删除postgreSQL中的口音的功能

前端之家收集整理的这篇文章主要介绍了删除postgreSQL中的口音的功能前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

Possible Duplicate:
07000

我试图删除重音,所以当我做一个SELECT它忽略它们.

例:

SELECT * FROM table WHERE
table.id ILIKE 'Jose';

它返回:

José
Jose
Jósé
Jóse

或类似的东西.

我发现这些功能,他们没有工作,我认为这可能是我使用Struts 1.X的事实,请检查出来,告诉我我错了什么或应该使用什么其他功能.

第一功能

CREATE OR REPLACE FUNCTION unaccent_string(text) RETURNS text AS $$
DECLARE
    input_string text := $1;
BEGIN

input_string := translate(input_string,'âãäåāăąÁÂÃÄÅĀĂĄ','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;

第二功能

CREATE OR REPLACE FUNCTION norm_text_latin(character varying) 
  RETURNS character varying AS 
$BODY$
declare 
        p_str    alias for $1; 
        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,'ÒÓÔÕÖ','OOOOO') into v_str; 
        select translate(v_str,'ÙÚÛÜ','UUUU') into v_str; 
        select translate(v_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,'òóôõö','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;

当我在NetBeans中运行文件时,这两个函数都会生成以下错误

ERROR: unterminated dollar-quoted string at or near "*the string that starts the function*"
使用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');

通过产生一个不重要的,较低的指数来加速事情:

somedb=# CREATE INDEX CONCURRENTLY ON table (lower(unaccent(id)));

猜你在找的Postgre SQL相关文章