CODEWITHSUNDEEP
sqlms-accesssql-like
SarahB
SarahB

Reputation: 328

Finding records with junk characters in MS Access SQL

I am trying to write an SQL query in MS Access to select where a text column has any punctuation characters except "_", "-","@", ".".

That is an underscore, hyphen, @ and a period are not allowed. Numbers and alphabets are not ok too. Everything else is to be returned

The below is my code:

select * from Employee
where name Like '*[!0-9,!a-z,!@,!A-Z,!.,!_,!-]*'

However, the above code is not working. Some of the names have "_" an underscore, and that is being selected as well. Any inputs?

Upvotes: 0

Views: 348

Answers (1)

Erik A
Erik A

Reputation: 32642

Your like expression is broken. You're currently selecting everything where there's at least one character that's not an underscore (or one of your other characters).

You should invert the condition, not select records where there's at least one character that's an underscore (or one of your other characters):

select * from Employee
where name Not Like '*[0-9,a-z,@,A-Z,.,_,-]*'

Upvotes: 4

Related Questions