REGEXP_REPLACE Punctuation in Redshift

Question

I'm trying to use REGEXP_REPLACE to remove all punctuation from a varchar. I'm using the following:

regexp_replace(d.NAME, [.,\/#!$%\^&\*;:{}=\-_`~()])

But it gives me an error, saying:

Statement 1 is not valid. ERROR: syntax error at or near "."

How can I fix this to remove all punctuation?

Solution

Firstly, the dash in a character class means a range, except when it's first or last... so put it there:

[.,\/#!$%\^&\*;:{}=\_`~()-]

And, you have to put it in quotes, and most characters don't need escaping:

regexp_replace(d.NAME, '[.,/#!$%^&*;:{}=_`~()-]')

Comments

[acs254]

Worked like a charm, my friend. There don't seem to be many good resources on regex...what's your go-to source?

[Bill Wallis]

The site regexr.com is my go-to for any regex testing -- I can't recommend it enough.