替换 PostgreSQL 中的 unicode 字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15190078/
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
Replace unicode characters in PostgreSQL
提问by user1923631
Is it possible to replace all the occurrences of a given character (expressed in unicode) with another character (expressed in unicode) in a varchar field in PostgreSQL?
是否可以在 PostgreSQL 的 varchar 字段中用另一个字符（以 unicode 表示）替换给定字符（以 unicode 表示）的所有出现？
I tried something like this:
我试过这样的事情：
UPDATE mytable
SET myfield = regexp_replace(myfield, '\u0050', '\u0060', 'g')
But it seems that it really writes the string '\u0060' in the field and not the character corresponding to that code.
但看起来它确实在字段中写入了字符串 '\u0060' 而不是该代码对应的字符。
回答by mvp
According to the PostgreSQL documentation on lexical structure, you should use U&syntax:
根据有关词法结构的PostgreSQL 文档，您应该使用U&语法：
UPDATE mytable
SET myfield = regexp_replace(myfield, U&'regress=> SELECT '\u0050', E'\u0050', U&'UPDATE mytable
SET myfield = translate(myfield, 'P', '`') -- actual characters
WHERE myfield <> translate(myfield, 'P', '`');
50';
?column? | ?column? | ?column?
----------+----------+----------
\u0050 | P | P
(1 row)
50', U&'UPDATE mytable
SET myfield = translate(myfield, U&'##代码##50', U&'##代码##60')
WHERE myfield <> translate(myfield, U&'##代码##50', U&'##代码##60');
60', 'g')
You can also use the PostgreSQL-specific escape-string form E'\u0050'. This will work on older versions than the unicode escape form does, but the unicode escape form is preferred for newer versions. This should show what's going on:
您还可以使用 PostgreSQL 特定的转义字符串形式E'\u0050'。这将适用于比 unicode 转义形式更旧的版本，但 unicode 转义形式更适用于较新的版本。这应该显示发生了什么：
回答by Erwin Brandstetter
It should work with the "characters corresponding to that code" unless come client or other layer in the food-chain mangles your code!
它应该与“对应于该代码的字符”一起工作，除非客户端或食物链中的其他层破坏了您的代码！
Also, use translate()or replace()for this simple job. Much faster than regexp_replace(). translate()is also good for multiple simple replacements at a time.
And avoid empty updateswith a WHEREclause. Much faster yet, and avoids table boat and additional VACUUMcost.
此外，使用translate()或replace()用于这个简单的工作。比 快得多regexp_replace()。translate()也适用于一次进行多个简单的替换。
并避免使用子句进行空更新WHERE。速度要快得多，并且避免了桌船和额外VACUUM费用。
If you keep running into problems, use the encoding @mvpprovided:
如果您一直遇到问题，请使用提供的编码@mvp：
##代码##