Redshift: How to remove non-printable characters

Question

I'm trying to remove non-printable characters from a string in Redshift and tried the TRANSLATE function but didn't return the results I'm looking for. Any advice?

Answer 1

Not quite right, this works:

regexp_replace(<col>, '[^[:alnum:][:blank:][:punct:]]', '')

It's a positive filter that will only allow ASCII, letters, numbers, spaces and punctuation.

Answer 2

Here's a regexp I've used to get down to just the chars:

regexp_replace(<col>,'[^[:print:]]|[[:cntrl:]]|[[:blank:]]','')

Don't know if this is what you are looking for but a place to start.