CODEWITHSUNDEEP
sql-servervalidationwildcard
jawz101
jawz101

Reputation: 91

mssql search a varchar field for invalid typo

I have a field with names in it. They can be last name, first name middle name/initial

Basically I want to find all names that aren't normal spellings so I can tell someone to fix their names in the system.

I don't want to select and find this guy O'Leary-Smith, Timothy L.

But I would want to find this guy <>[]}{@#$%^&*()/?=+_!|";:~`1234567890

I can just keep coming up with special characters to search for but then I'm just making this huge query and having to bracket wildcards... it's like 50+ lines long just to say one thing.

Is there something (not some custom function) that lets me say

where name not like 
A-Z
a-z
,
.
'
-

possibly something that is 

where name contains anything but these ascii characters

Upvotes: 1

Views: 97

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15150

If it's just odd characters you're looking for:

WHERE name like '%[^A-Za-z]%'

The ^ acts as a NOT operator.

Upvotes: 1

Alex K.
Alex K.

Reputation: 175916

Hopefully this is a one of fix-up; a negated character class:

where patindex('%[^ A-Za-z,.''-]%', name) > 0

Although more letters than A-Z can appear in names ...

Upvotes: 1

Related Questions