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?

FWIW I'm using postgresql. Bonus points if you can also show me how to force all inserts and updates to afterwards perform the same modification, without ending up with three slashes. — Dan Burton
– Dan Burton, Commented Jun 2, 2011 at 22:47
You're better off storing the values in the database in Unicode and perform the escaping as and when needed - it'll be less complex at the end of the day. — Will A
– Will A, Commented Jun 2, 2011 at 23:03
This sounds really suspect to me. Why do you “need” to have encoded data in the database? This usually implies the code putting the data in or getting it back out has some serious problems. Data should normally be kept in raw unescaped text format. — bobince
– bobince, Commented Jun 2, 2011 at 23:05
@will I'm allowed to mess with the db, but not with any of the software that retrieves and handles the data. I'm aware it has serious problems, but it's sadly not in my power to fix those problems. — Dan Burton
– Dan Burton, Commented Jun 2, 2011 at 23:37

Grzegorz Szpetkowski · Accepted Answer · 2011-06-03 00:01:34Z

10

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

edited Jun 3, 2011 at 0:01

answered Jun 2, 2011 at 23:55

Grzegorz Szpetkowski

38.1k6 gold badges94 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Burton Over a year ago

Thanks. I made two variants of this: one to handle a string beginning with a unicode escape sequence, and another that will preserve the character preceding the \u (the version you provide consumes it). I also had to double the number of backslashes since our db doesn't use standard_conforming_strings. Icky stuff, but it worked.

Grzegorz Szpetkowski Over a year ago

@Dan: You can also use dollar quoting $$pattern$$ to avoid that doubled backslashes postgresql.org/docs/9.0/static/…

Will A · Accepted Answer · 2011-06-02 22:44:51Z

1

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

answered Jun 2, 2011 at 22:44

Will A

25k5 gold badges54 silver badges61 bronze badges

2 Comments

Dan Burton Over a year ago

+1 This does look useful, but it appears it is incapable of detecting whether or not the double-escape is already performed.

Will A Over a year ago

...and therein lies the problem, Dan - please take a moment to seriously consider storing the data in a more appropriate format.

Collectives™ on Stack Overflow

Regex replacement in PostgreSQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related