How to replace a single quote character with two single quote characters

Question

I want to replace all instances of a string quote (') with two single quotes ('') in a string.

Lets say e'QmfgLu/]sf]/sd is a string and I want to replace ' with ''.

The result must be e''OmfgLu/]

I tried this query:

update customer set name=REGEXP_REPLACE(name, E'\'', '''');

also

 update customer set name=REPLACE(name, E'\'', '''');

This query is not properly working. What is the suitable way to write the query?

Answer 1

You may replace a single occurrence of single quotes with 2 quotes using this regexp.

update customer set name=REGEXP_REPLACE(name, $$([^'])'([^'])$$, $$\1''\2$$ ,'g');

$$([^'])'([^'])$$ - represents a sequence of any character other than a single quote followed by a quote and then a non-quote character.

I'm using the dollar quoting to avoid confusing quotes.

Demo

EDIT

As @edruid pointed out, to handle case for quotes at the start and end of string, use: REGEXP_REPLACE(name, $$([^']|^)'(?!')$$, $$\1''$$ ,'g')

This uses a negative lookahead for matching a single quote - (?!')

Demo2

Answer 2

In postgres the way to have a single quote in a string is to type '' (' is used as the escape character) so your replace would be

update customer set name=REGEXP_REPLACE(name, E'''', '''''', 'g');

(skip the final 'g' if you only want to replace the first ')

or without resorting to regexp:

update customer set name=REPLACE(name, '''', '''''');