SQL like - select strings that contain unspecified characters

Question

First of all, apologies if this is a duplicate question. I've done my best to search but was unsuccessful, and I couldn't even properly word my question in terms of keywords!

One of my tables has a column Name (nvarchar). I want to find out which rows contain special characters without explicitly listing those characters. "Special" characters in my case means anything not in:

a-z A-Z 0-9 _ @ . , ( ) % + -

So for example:

Row 1: 'asdf Asdf 0123'
Row 2: 'asdf (Asdf) 012/3'
Row 3: 'zxcv [234]'
Row 4: 'asdf #0123'

I want to select rows 2, 3 and 4.

The easiest way is to include the characters I don't want, for example square brackets and slash:

SELECT * FROM Table
WHERE Name LIKE '%[\]\[/]%' ESCAPE '\'

This returns rows 2 and 3, or if I use NOT LIKE, rows 1 and 4. However, I also want to find other characters which I may not have thought of (such as the #). Listing the characters that ARE wanted -

SELECT * FROM Table
WHERE Name NOT LIKE '%[a-zA-Z0-9_@. ,()%+-]%'

doesn't work either, as it returns 0 results since all rows contain at least 1 of those characters.

Is there a way to restrict the latter LIKE statement not to match any string that contains my desired characters, but rather strings that contain only the desired characters and nothing else?

Solution

In MySQL you can do:

WHERE Name REGEX '[^-a-zA-Z0-9.,()%+]'

Not sure if the same REGEX operator exists in SQL Server, but it probably has something similar.

Comments

[Boris]

Just did WHERE Name LIKE '%[^a-zA-Z0-9_@. ,()%+-]%' as per this page w3schools.com/sql/sql_wildcards.asp . I had already seen - and tried - this prior to asking this question but for some reason hadn't gotten it right. Either way, this is pretty much what I was looking for so thanks! :)

[Boris]

I think this is unnecessarily complex; I used LIKE and the ^ wildcard as shown here w3schools.com/sql/sql_wildcards.asp . Thanks anyway! :)

[Matt]

I'd check out the performance of LIKE vs PATINDEX - its not more complex than a like really, just a function call instead of a predicate. I've read mixed results about the relative performance of the two methods, one of them might give you faster results...

[Boris]

I meant longer and more difficult to write/understand :) The script needs to be shared with others who will use it just once, so my primary concern is simplicity/readability, not performance.