t-sql "LIKE" and Pattern Matching

I've found a small annoyance that I was wondering how to get around...

In a simplified example, say I need to return "TEST B-19" and "TEST B-20"

I have a where clause that looks like:

where [Name] LIKE 'TEST B-[12][90]'

and it works... unless there's a "TEST B-10" or "TEST-B29" value that I don't want.

I'd rather not resort to doing both cases, because in more complex situations that would become prohibitive.

I tried:

where [Name] LIKE 'TEST B-[19-20]'

but of course that doesn't work because it is looking for single characters...

Thoughts? Again, this is a very simple example, I'd be looking for ways to grab ranges from 16 to 32 or 234 to 459 without grabbing all the extra values that could be created.

EDITED to include test examples...

You might see "TEXAS 22" or "THX 99-20-110-B6" or "E-19" or "SOUT开发者_开发知识库HERN B" or "122 FLOWERS" in that field. The presense of digits is common, but not a steadfast rule, and there are absolutely no general patterns for hypens, digits, characters, order, etc.

---

I would divide the Name column into the text parts and the number parts, and convert the number parts into an integer, and then check if that one was between the values. Something like:

where cast(substring([Name], 7, 2) as integer) between 19 and 20

And, of course, if the possible structure of [Name] is much more complex, you'd have to calculate the values for 7 and 2, not hardcode them....

EDIT: If you want to filter out the ones not conforming to the pattern first, do the following:

where [Name] LIKE '%TEST B-__%'
and cast(substring([Name], CHARINDEX('TEST B-', [Name]) + LEN('TEST B-'), 2) as integer) between 19 and 20

Maybe it's faster using CHARINDEX in place of the LIKE in the topmost line two, especially if you put an index on the computed value, but... That is only optimization... :)

EDIT: Tested the procedure. Given the following data:

jajajajajajajTEST B-100
jajajajajajajTEST B-85
jajajajjTEST B-100
jajjajajTEST B-100
jajajajajajajTEST B-00
jajajajaTEST B-100
jajajajajajajEST B-99
jajajajajajajTEST B-100
jajajajajajajTEST B-19
jajajajjTEST B-100
jajjajajTEST B-120
jajajajajajajTEST B-00
jajajajaTEST B-150
jajajajajajajEST B-20
TEST B-20asdfh asdfkh

The query returns the following rows:

jajajajajajajTEST B-19
TEST B-20asdfh asdfkh

---

Wildcards or no, you still have to edit the query every time you want to change the range definition. If you're always dealing with a range (and it's not always the same range), you might use parameters. For example:

note: for some reason (this has happened in many other posts as well), when I try to post code beginning with 'declare', SO hangs and times-out. I reported it on meta already, but nobody could reproduce it (including me). Here it's happening again, so I took the 'D' off, and now it works. I'll come back tomorrow, and it will let me put the 'D' back on.

DECLARE @min varchar(5)
DECLARE @max varchar(5)

SET @min = 'B-19'
SET @max = 'B-20'

SELECT
   ...
WHERE NAME BETWEEN @min AND @max

You should avoid formatting [NAME] as others have suggested (using function on it) -- this way, your search can benefit from an index on it.

In any case -- you might re-consider your table structure. It sounds like 'TEST B-19' is a composite (non-normalized) value of category ('TEST') + sub-category ('B') + instance ('19'). Put it in a lookup table with 4 columns (id being the first), and then join it by id in whatever query needs to output the composite value. This will make searching and indexing much easier and faster.

---

In the absence of test data, I generated my own. I just removed the Test B- prefix, converted to int and did a Between

With Numerals As
(
    Select top 100 row_number() over (order by name)  TestNumeral
    from sys.columns
),
TestNumbers AS
(
    Select 'TEST B-' + Convert (VarChar, TestNumeral) TestNumber
    From Numerals
)
Select * 
From TestNumbers
Where Cast (Replace (TestNumber, 'TEST B-', '') as Integer) between 1 and 16

This gave me

TestNumber
-------------------------------------
TEST B-1
TEST B-2
TEST B-3
TEST B-4
TEST B-5
TEST B-6
TEST B-7
TEST B-8
TEST B-9
TEST B-10
TEST B-11
TEST B-12
TEST B-13
TEST B-14
TEST B-15
TEST B-16

This means, however, that if you have different strategies for naming tests, you would have to remove all different kinds of prefixes.

Now, on the other hand, if your Test numbers are in the TEST-Space-TestType-Hyphen-TestNumber format, you could use PatIndex and SubString

With Numerals As
(
    Select top 100 row_number() over (order by name)  TestNumeral
    from sys.columns
),
TestNumbers AS
(
    Select 'TEST B-' + Convert (VarChar, TestNumeral) TestNumber
    From Numerals
    Where TestNumeral Between 10 and 19
    UNION
    Select 'TEST A-' + Convert (VarChar, TestNumeral) TestNumber
    From Numerals
    Where TestNumeral Between 20 and 29
)
Select *
From TestNumbers
Where Cast (SubString (TestNumber, PATINDEX ('%-%', TestNumber)+1, Len (TestNumber) - PATINDEX ('%-%', TestNumber)) as Integer) between 16 and 26

That should yield the following

TestNumber
-------------------------------------
TEST A-20
TEST A-21
TEST A-22
TEST A-23
TEST A-24
TEST A-25
TEST A-26
TEST B-16
TEST B-17
TEST B-18
TEST B-19

All of your examples seem to have the test numbers at the end. So if you can create a table of patterns and then JOIN using a LIKE statement, you may be able make it work. Here is an example:

;
With TestNumbers As
(
      select 'E-1' TestNumber
union select 'E-2'
union select 'E-3'
union select 'E-4'
union select 'E-5'
union select 'E-6'
union select 'E-7'
union select 'SOUTHERN B1'
union select 'SOUTHERN B2'
union select 'SOUTHERN B3'
union select 'SOUTHERN B4'
union select 'SOUTHERN B5'
union select 'SOUTHERN B6'
union select 'SOUTHERN B7'
union select 'Southern CC'
union select 'Southern DD'
union select 'Southern EE'
union select 'TEST B-1'
union select 'TEST B-2'
union select 'TEST B-3'
union select 'TEST B-4'
union select 'TEST B-5'
union select 'TEST B-6'
union select 'TEST B-7'
union select 'TEXAS 1'
union select 'TEXAS 2'
union select 'TEXAS 3'
union select 'TEXAS 4'
union select 'TEXAS 5'
union select 'TEXAS 6'
union select 'TEXAS 7'
union select 'THX 99-20-110-B1'
union select 'THX 99-20-110-B2'
union select 'THX 99-20-110-B3'
union select 'THX 99-20-110-B4'
union select 'THX 99-20-110-B5'
union select 'THX 99-20-110-B6'
union select 'THX 99-20-110-B7'
union select 'Southern AA'
union select 'Southern CC'
union select 'Southern DD'
union select 'Southern EE'
),
Prefixes as
(
    Select 'TEXAS ' TestPrefix
    Union Select 'THX 99-20-110-B'
    Union Select 'E-'
    Union Select 'SOUTHERN B'
    Union Select 'TEST B-'
)
Select TN.TestNumber
From TestNumbers TN, Prefixes P
Where 1=1 
And TN.TestNumber Like '%' + P.TestPrefix + '%'
And Cast (REPLACE (Tn.TestNumber, p.TestPrefix, '') AS INTEGER) between 4 and 6

This will give you

TestNumber
----------------
E-4
E-5
E-6
SOUTHERN B4
SOUTHERN B5
SOUTHERN B6
TEST B-4
TEST B-5
TEST B-6
TEXAS 4
TEXAS 5
TEXAS 6
THX 99-20-110-B4
THX 99-20-110-B5
THX 99-20-110-B6

(15 row(s) affected)

---

Is this acceptable:

WHERE [Name] IN ( 'TEST B-19', 'TEST B-20' )

The list of values can come from a subquery, e.g.:

WHERE [Name] IN ( SELECT [Name] FROM Elsewhere WHERE ... )