Regex replacement in PostgreSQL

Question

I have a table where a particular string field often includes unicode for single and double quotes inside of it: \u0027 and \u0022 respectively. So it turns out, I actually need them escaped even more. I need to put an extra \ in front of them.

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

What kind of SQL could perform this kind of an update on the table for all records?

Answer 1

If you really need this, then you can use such RE:

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

Make sure that standard_conforming_strings is enabled and regex_flavor is set to advanced.

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

Replacement string '\\\\\1' means two following backslashes \\ and \1 represent first (reporting) parenthesized subexpression (that is, 'u' concatenated with four digits from pattern).