替换 PostgreSQL 中的 unicode 字符 [英] Replace unicode characters in PostgreSQL

问题描述

是否可以在 PostgreSQL 的 varchar 字段中用另一个字符(以 unicode 表示)替换给定字符(以 unicode 表示)的所有出现?

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?

我尝试过这样的事情:

UPDATE mytable 
SET myfield = regexp_replace(myfield, 'u0050', 'u0060', 'g')

但是好像真的是在字段中写了字符串'u0060'，而不是那个code对应的字符.

But it seems that it really writes the string 'u0060' in the field and not the character corresponding to that code.

推荐答案

根据关于词法结构的PostgreSQL文档，你应该使用U&语法:

According to the PostgreSQL documentation on lexical structure, you should use U& syntax:

UPDATE mytable 
SET myfield = regexp_replace(myfield, U&'050', U&'060', 'g')

您还可以使用 PostgreSQL 特定的转义字符串形式 E'u0050'.这将适用于比 unicode 转义形式更旧的版本，但 unicode 转义形式更适用于较新的版本.这应该显示发生了什么:

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:

regress=> SELECT 'u0050', E'u0050', U&'050';
 ?column? | ?column? | ?column? 
----------+----------+----------
 u0050   | P        | P
(1 row)

这篇关于替换 PostgreSQL 中的 unicode 字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！