regex to replace backslash and single quote with single quote using postgres regexp_replace()

Question

Just as the title states, I'm not the best w/ regex, so can anyone provide the appropriate regex for the following:

UPDATE table SET column = REGEXP_REPLACE(column, {regex}, '''');

Basically, I'd like to replace any instances of backslashes (\) followed by one or more single quotes with one single quote.

So, the string Hello World\'s or Hello World\'''''s should become Hello World's, but not Hello World\s.

score 9 · Accepted Answer · answered Jan 14 '15 at 18:39

9

This is relatively straightforward. Note that both the backslash character \ as well as the single-quote character ' (which you escaped in the OP) need to be escaped. The difference is that the backslash has to be escaped in the regex itself, whereas the single quote is escaped in the string literal. Anyway, enough of that digression.

UPDATE table SET column = REGEXP_REPLACE(column, '\\''+', '''', 'g');

Hope this helps.

answered Jan 14 '15 at 18:39

David Faber

12,277
2
29
40

1

I ended up using this and accepted, I appreciate the explanation as well. – William Orazi Jan 14 '15 at 19:46

zen · Answer 2 · 2015-01-15T09:01:49.610

3

You can try the following:

SELECT REGEXP_REPLACE(column, '\\''['']*', '''','g') from table

Edit: of course \''+ is better

SELECT REGEXP_REPLACE(column, '\\''+', '''','g') from table

edited Jan 15 '15 at 09:01

answered Jan 14 '15 at 18:12

zen

250
2
8

2

and why not `'\\''+'` ? – Casimir et Hippolyte Jan 14 '15 at 18:14

regex to replace backslash and single quote with single quote using postgres regexp_replace()

2 Answers2