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
– Laurel, Commented Oct 20, 2016 at 0:54

Bohemian · Accepted Answer · 2016-10-20 02:06:18Z

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, 2016 at 2:06

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

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

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