Solved

SQL Pattern Matching

Posted on 2014-04-25
3
293 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
3 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
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:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now