Searching For Special Characters in postgresql

Question

i am trying to find rows in a postgresql table where a specific column contains special characters excluding the following @^$.!\-#+'~_

any help appreciated

Answer 1

Hi I think I figured it out. I found a solution that worked for me using Posix Regular Expressions.

SELECT *
FROM TABLE_NAME
WHERE fieldName ~ '[^A-Za-z0-9@^\\$.!\-#+~_]'

The regular expression matches any character that is not between A-Z, a-z, 0-9 and is also not any of your whitelisted characters ^$.!-#+~_. Notice that in the regex I had to escape the backslash and the hyphen, because they have a special meaning in regex. Maybe start by evaluating my proposed regex online with a few examples, e.g. here: https://regex101.com