Jerry Miller
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-Z 0-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-Z 0-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-Z 0-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-Z 0-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-Z 0-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-Z 0-9][a-zA- Z0-9][a-zA -Z0-9]')
OR (((CustNum) Is Null))
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]
And (CustNum) Not Like 'CCE[a-zA-Z0-9][a-zA-Z0-9]
And (CustNum) Not Like '110[a-zA-Z0-9][a-zA-Z0-9]
And (CustNum) Not Like 'Q[a-zA-Z0-9][a-zA-Z0-9][a
And (CustNum) Not Like 'USC[a-zA-Z0-9][a-zA-Z0-9]
And (CustNum) Not Like 'BNM[a-zA-Z0-9][a-zA-Z0-9]
OR (((CustNum) Is Null))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Open in new window
HTH,Dan