在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')

但是似乎它确实写了字符串'\

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&'\0050', U&'\0060', '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&'\0050';
 ?column? | ?column? | ?column? 
----------+----------+----------
 \u0050   | P        | P
(1 row)

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