Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Pattern Matching

Posted on 2014-04-25
3
Medium Priority
?
327 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 70

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

636 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