Link to home
Start Free TrialLog in
Avatar of Jerry Miller
Jerry MillerFlag for United States of America

asked on

SQL Pattern Matching

I am looking for the best way to exclude a list of specific patterns from my query. I know that I can do things like the examples below, but I wondering if there wasn't a better way to get this working.

I am looking to return strings that do not start with the strings below and are 11 alpha-numeric characters. I need to return values that do not match the pattern or are NULL, so I can't use 582% for example. It doesn't check the number of characters.

Any ideas?

WHERE ((CustNum) Not Like '582________'
      And (CustNum) Not Like 'CCE________'
      And (CustNum) Not Like '110________'
      And (CustNum) Not Like 'Q__________'
      And (CustNum) Not Like 'USC________'
      And (CustNum) Not Like 'BNM________')
      OR (((CustNum) Is Null))

OR
WHERE ((CustNum) Not Like '582[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
      And (CustNum) Not Like 'CCE[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
      And (CustNum) Not Like '110[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
      And (CustNum) Not Like 'Q[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
      And (CustNum) Not Like 'USC[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
      And (CustNum) Not Like 'BNM[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]')
      OR (((CustNum) Is Null))
Avatar of Dan Craciun
Dan Craciun
Flag of Romania image

If you can use a RegEx CLR, then the pattern is clear:
"((582|CCE|110|USC|BNM)\w{8})|Q\w{10}"

Open in new window

HTH,
Dan
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jerry Miller

ASKER

Exactly what I needed, thanks Scott! The query in the stored procedure is small and best of all it is scalable by simply adding / removing values in the table.