Regex replacement in PostgreSQL

Question

I have a table in which a particular line field often includes unicode for single and double quotes inside it: \u0027 and \u0022 respectively. So it turns out, I really need them to slip away even more. I need to put in front of him \ .

For example, I need to change \u0027Hello, world\u0027 to \\u0027Hello, world\\u0027

What SQL could perform this type of update for the table for all records?

Answer 1

If you really need it, you can use a RE like this:

 UPDATE table SET c = regexp_replace(c, '[^\\]\\(u\d{4})', '\\\\\1', 'g'); 

Make sure standard_conforming_strings is turned on and regex_flavor is set to advanced.

 SHOW standard_conforming_strings; standard_conforming_strings ----------------------------- on (1 row) 

The '\\\\\1' string '\\\\\1' means the next two backslashes \\ and \1 represent the first (reporting) subexpression in parentheses (i.e. 'u' , combined with the four digits from the pattern).

Answer 2

The UPDATE statement with SET yourcolumn = REPLACE(yourcolumn, '\u0027', '\\u0027') should do this. First try below to verify that it works before performing a bulk upgrade.

 SELECT REPLACE('\u0027', '\u0027', '\\u0027')