Replace unicode characters in PostgreSQL

Question

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?

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.

Answer 1

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

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)