CODEWITHSUNDEEP
sqlsql-serverregex
iarp
iarp

Reputation: 546

Find all but allowed characters in column

I have a query I've been trying to make, it's supposed to pull all rows that do not contain characters we do not want.

SELECT NID FROM NOTES WHERE NOTE LIKE '%[^0-9a-zA-Z#.;:/^\(\)\@\ \  \\\-]%'

That should return any rows that do not contain 

0-9 a-z A-z . : ; ^ & @ \ / ( ) #

But any time i add one of these below it fails

$ [ ] ?

Even trying to escape them either by \ or [[ doesn't seem to work properly. I only have access to stock SQL install.

Upvotes: 4

Views: 4161

Answers (1)

Mark Byers
Mark Byers

Reputation: 838146

it's supposed to pull all rows that do not contain characters we do not want.

To find rows that contain x you can use LIKE:

SELECT * FROM yourtable WHERE col LIKE '%x%'

To find rows that do not contain x you can use NOT LIKE:

SELECT * FROM yourtable WHERE col NOT LIKE '%x%'

So your query should use NOT LIKE because you want rows that don't contain something:

SELECT NID FROM NOTES WHERE NOTE NOT LIKE '%[0-9a-zA-Z#.;:/^\(\)\@\ \  \\\-]%'

That should return any rows that do not contain

0-9 a-z A-z . : ; ^ & @ \ / ( ) #

No. Because of the ^ at the start, it returns the rows that don't contain characters except those. Those characters you listed are the characters that are allowed.

Upvotes: 5

Related Questions