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))
LVL 18
Jerry MillerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
The easiest way is to put the exclusion values into a table, then use NOT EXISTS on that table:


SELECT ...
FROM dbo.main m
WHERE
    NOT EXISTS(
        SELECT 1
        FROM dbo.exclusions e
        WHERE
            e.pattern IS NOT NULL AND
            m.Custnum LIKE e.pattern
    ) AND
    ...
0
 
Dan CraciunIT ConsultantCommented:
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
0
 
Jerry MillerAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.