CODEWITHSUNDEEP
postgresqlregexp-replace
Tal Sibony
Tal Sibony

Reputation: 3

How to remove special characters from a string in postgresql

I am trying to remove using REGEXP_REPLACE the following special characters: "[]{}

from the following text field: [{"x":"y","s":"G_1","cn":"C8"},{"cn":"M2","gn":"G_2","cn":"CA99"},{"c":"ME3","gn":"G_3","c":"CA00"}] and replace them with nothing, not even a space.

*Needless to say, this is just an example string, and I need to find a consistent solution for similar but different strings.

I was trying to run the following: SELECT REGEXP_REPLACE('[{"x":"y","s":"G_1","cn":"C8"},{"cn":"M2","gn":"G_2","cn":"CA99"},{"c":"ME3","gn":"G_3","c":"CA00"}] ','[{[}]":]','')

But received pretty much the same string..

Thanks in advance!

Upvotes: 0

Views: 1213

Answers (1)

JGH
JGH

Reputation: 17906

You need to escape the special characters (\), and to specify that you want to repeat the operation for every characters ('g') else it will stop at the 1st match

 SELECT REGEXP_REPLACE(
    '[{"x":"y","s":"G_1","cn":"C8"},{"cn":"M2","gn":"G_2","cn":"CA99"},{"c":"ME3","gn":"G_3","c":"CA00"}] ',
     '[{\[}\]":]',
     '',
     'g');
                  regexp_replace
--------------------------------------------------
 xy,sG_1,cnC8,cnM2,gnG_2,cnCA99,cME3,gnG_3,cCA00
(1 row)

Upvotes: 0

Related Questions