?
Solved

SQL Pattern Matching

Posted on 2014-04-25
3
Medium Priority
?
318 Views
Last Modified: 2014-04-28
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))
0
Comment
Question by:Jerry Miller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 40023619
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40027625
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
 
LVL 18

Author Closing Comment

by:Jerry Miller
ID: 40027988
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

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question