1

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

any help appreciated

zarek37
  • 27
  • 3
  • Hi zarek, did you mean ALL UPPERCASE and ALL LOWERCASE text? "ALLUPPERCASE" => true "NOTALLuppercase" => false "alllowecase" => true "alllowercasebutspecial_char" => false Maybe you can give a few examples :) – yezper Aug 19 '22 at 08:03
  • thank you for the feedback, i changed my question to make it less ambiguous. Thanks again – zarek37 Aug 19 '22 at 08:10

1 Answers1

2

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

yezper
  • 693
  • 3
  • 12