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模块.
原文链接:https://www.f2er.com/postgresql/192707.htmlsomedb=# 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)));