CODEWITHSUNDEEP
sqlregexpostgresqlreplace
Dan Burton
Dan Burton

Reputation: 53715

Regex replacement in PostgreSQL

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?

Upvotes: 4

Views: 7153

Answers (2)

Grzegorz Szpetkowski
Grzegorz Szpetkowski

Reputation: 37954

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

Upvotes: 10

Will A
Will A

Reputation: 25008

An UPDATE statement with SET yourcolumn = REPLACE(yourcolumn, '\u0027', '\\u0027') ought to do it. Try the below first to check that it works before doing a mass update.

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

Upvotes: 1

Related Questions