PostgreSQL Query for Non Alphanumeric

Question

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.

Bookrack

Answer 1

I don't believe that Postgres LIKE supports regex such as the following:

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

But you can instead use the tilde operator to access full regex. Expressed verbally, I think the logic you want here is:

Find any string which does not have one or more non alphanumeric characters present in the string.

Use a regex pattern with the right pattern which covers the entire string:

SELECT DESCRIPTION
FROM ITEM
WHERE DESCRIPTION ~ '^.*[^A-Za-z0-9 .-].*$'

This would match any description having one or more characters which are not letters, numbers, space, dot or dash.

Conjecture

I think you mean WHERE DESCRIPTION ~ and not WHERE DESCRIPTION !~ – Indurate

I agree with your comment. – Conjecture

Answer 2

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 .-]%'

Extremely