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?

@vkp That would also replace whitespace, numbers, and letters like `ñ`. That's not acceptable in most cases. — Laurel, Oct 20 '16 at 00:54

score 8 · Accepted Answer · answered Oct 20 '16 at 02:06

8

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, '[.,/#!$%^&*;:{}=_`~()-]')

answered Oct 20 '16 at 02:06

Bohemian

Worked like a charm, my friend. There don't seem to be many good resources on regex...what's your go-to source? – acs254 Oct 20 '16 at 15:54
The site [regexr.com](https://regexr.com/) is my go-to for any regex testing -- I can't recommend it enough. – Bilbottom Dec 24 '20 at 17:06

1 Answers1