postgresql 如何在 Postgres 9.5 中替换多个特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38619072/
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
How to replace multiple special characters in Postgres 9.5
提问by johan855
I have a table containing a list of name which might contain special character:
我有一个包含可能包含特殊字符的名称列表的表：
id name
1 Joh?n
2 Jürgen
3 Janna
4 üdyr
...
Is there a function that replaces each character for another specific one? (Not necessarily an unaccented one). Something like this:
是否有将每个字符替换为另一个特定字符的函数？（不一定是不带口音的）。像这样的东西：
SELECT id, function('?,ü',name,'ae,ue');
Result:
id name
1 Johaen
2 Juergen
3 Janna
4 UEdyr
...
回答by Pavel Stehule
No, there are no this function. Probably is not to hard to write optimized C extension what does it. But C language is not necessary always. You can try SQL or PLpgSQL function:
不，没有这个功能。大概不是很难写出优化的C扩展有什么用。但 C 语言并非总是必要的。您可以尝试 SQL 或 PLpgSQL 函数：
CREATE OR REPLACE FUNCTION xx(text, text[], text[])
RETURNS text AS $$
SELECT string_agg(coalesce([array_position(, c)],c),'')
FROM regexp_split_to_table(,'') g(c)
$$ LANGUAGE sql;
postgres=# select xx('Jürgen', ARRAY['?','ü'], ARRAY['ae','ue']);
┌─────────┐
│ xx │
╞═════════╡
│ Juergen │
└─────────┘
(1 row)
On my comp it does 6000 transformation under 200ms (but I have developer build of PostgreSQL - it is slower).
在我的电脑上，它在 200 毫秒内进行了 6000 次转换（但我有 PostgreSQL 的开发人员版本 - 它更慢）。
回答by vitfo
replace()
代替（）
If you want just to replace one or few characters you can use function replace(string text, from text, to text) that replaces all occurrences in string substring. The replace function can be used to replace one character to several characters.
如果您只想替换一个或几个字符，您可以使用函数 replace(string text, from text, to text) 替换字符串子字符串中出现的所有字符。替换功能可用于将一个字符替换为多个字符。
translate()
翻译（）
If you want to translate some letters to other letters you can user function translate(string text, from text, to text) that replaces any character in a string that matches a character in the from by the corresponding character in the to set.
如果要将某些字母转换为其他字母，您可以使用函数 translate(string text, from text, to text) 将字符串中与 from 中的字符匹配的任何字符替换为 to 设置中的相应字符。
Some data to play with:
一些数据可以玩：
drop table if exists xyz;
create table xyz (
id serial not null,
name varchar(30)
);
insert into xyz (name) values
('Juh?n?o'),
('Jürgüen'),
('Dann?u'),
('übüdyr');
Example of replace function:
替换函数示例：
select replace(name, '?', 'a') from xyz;
This function replaces letter ? in the name column with letter a. Juh?n?o becomes Juhanao.
此函数替换字母 ? 在带有字母 a 的名称列中。Juh?n?o 成为 Juhanao。
select replace(name, '?', 'ae') from xyz;
Now it replaces letter ? with ae.
现在它取代了字母 ? 与 ae。
select replace(replace(replace(name, '?', 'ae'), 'ü', 'ue'), 'ü', 'Ue') from xyz;
Not very nice, but in the example all ? become ae, ü become ue, and ü become 'Ue'.
不是很好，但在示例中全部？变成ae，ü变成ue，ü变成'Ue'。
update xyz set name = replace(replace(replace(name, '?', 'ae'), 'ü', 'ue'), 'ü', 'Ue');
Changes letters and updates rows. The result of the update is following:
更改字母并更新行。更新结果如下：
Juhaenaeo
Juergueen
Dannaeu
Uebuedyr
Example of translate function:
翻译函数示例：
select translate(name, '?,ü,ü', 'a,u,U') from xyz;
Translates all letters ? to a, ü to u and ü to U.
翻译所有字母？到 a，ü 到 u 和 ü 到 U。
update xyz set name = translate(name, '?,ü,ü', 'a,u,U');
Updates table so all predefined letters are translated and the change is saved to the database. The result of the update is following:
更新表，以便翻译所有预定义的字母并将更改保存到数据库中。更新结果如下：
Juhanao
Jurguen
Dannau
Ubudyr
More information:
更多信息：
