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

regex postgresql regexp-replace
William Orazi picture William Orazi · Jan 14, 2015 · Viewed 12.4k times · Source

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.

Answer

David Faber picture David Faber · Jan 14, 2015

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.

Related questions

Extract numbers from a field in PostgreSQL

I have a table with a column po_number of type varchar in Postgres 8.4. It stores alphanumeric values with some special characters. I want to ignore the characters [/alpha/?/$/encoding/.] and check if the column contains a number or not. …

Read More
How to extract a substring pattern in Postgresql

I have a column with a lot of inconsistent strings. Some of them contain a substring with a consistent pattern of '2015mmdd_AB_CD_EFG_(text)_(text)_HIJ' which I would like to extract. I feel this is a …

Read More
PostgreSQL Regex Word Boundaries?

Does PostgreSQL support \b? I'm trying \bAB\b but it doesn't match anything, whereas (\W|^)AB(\W|$) does. These 2 expressions are essentially the same, aren't they?

Read More