PostgreSQL Query for Non Alphanumeric

user_7211775 0
Viewed 7075

I have been searching Stackoverflow for queries on how to find cells that have special characters.

I saw this on another post

SELECT * FROM myTable WHERE myField LIKE '%[^a-zA-Z0-9]%'

and patterend my code from it below

SELECT DESCRIPTION FROM ITEM WHERE DESCRIPTION NOT LIKE '%[a-zA-Z0-9 .-]%'

But it is not working for me with the below result showing '-' and '.' and whitespace.

"2016 LANCER EX GT-A 2.0G CVT"

"2016 MIRAGE GLX 1.2G MT"

"2016 MIRAGE G4 GLX 1.2G MT (UPG)"

"2016 MIRAGE G4 GLX 1.2G CVT (UPG)"

I tried changing it to

SELECT DESCRIPTION FROM ITEM WHERE DESCRIPTION ~* '%[^a-zA-Z0-9 ]%'

which returns 0 results.

Any help is appreciated. I am confused why it works for other people and not mine. I am using Postgresql and PGadmin on an Opensuse OS.

postgresql-9.2 opensuse pgadmin
2 Answers

Postgres can handle regex but it uses the keywords SIMILAR TO and NOT SIMILAR TO seen here.

I think what you want is SELECT DESCRIPTION FROM ITEM WHERE DESCRIPTION NOT SIMILAR TO '%[a-zA-Z0-9 .-]%'

user_111923990
Related