Search code examples
sqlpostgresqlreplaceregexp-replace

Postgresql regex_replace comma, single and double quotes in a single

I have a string which consists of double quotes, single quotes and commas. I would like to replace all the occurrences of them using regex_replace.

Tried 

REGEXP_REPLACE(translate (links, '"',''), '['''''',]'        , '')

It replaces the first occurrence of comma not the second one.

'https://google.com/khjdbgksdngksd#/","https://google.com/khjdbgksdngksd#/","'
Solution

  • Why are you mixing TRANSLATE and REGEXP_REPLACE? Just pick one and use it, as either one can do all that you want.

    If you want REGEXP_REPLACE to replace all instances, you have to give it a fourth argument (the flag argument) of 'g' for 'global', otherwise it stops after the first match and substitution.

    Also, to preserve sanity I would use dollar-quoting when the thing being quoted has single quote marks (which yours has in considerable excess).

    Using TRANSLATE is probably a better tool for the job, but your title was specifically about REGEXP_REPLACE, so:

    REGEXP_REPLACE(links, $$[',"]$$, '', 'g');