And I'll add one more...
Combining some of the code above along with some basic string manipulation, what I did was make an inner query that returned either side of the minus sign in two fields...
select Substring(KEY1,CharIndex('-',KEY1)) as KEYF,
Substring(KEY1,CharIndex('-',KEY1)+1,len(KEY1)) as KEYT
from TheDumbTable
where KEY1 like '%-%'
That returns something like...
-------------
|KEYF |KEYT |
-------------
| S10 | S30 |
| S19 | S40 |
-------------
Next, I wrap that in another query that uses PATINDEX to separate the numbers from numerics...
SELECT cast(substring(KEYF,patindex('%[0-9]%', KEYF),len(KEYF)) as int) as KEYFINT,
cast(substring(KEYT,patindex('%[0-9]%', KEYT),len(KEYT)) as int) as KEYTINT,
replace(KEYF,substring(KEYF,patindex('%[0-9]%', KEYF),len(KEYF)),'') as KEYTEXT
FROM
(select Substring(KEY1,CharIndex('-',KEY1)) as KEYF,
Substring(KEY1,CharIndex('-',KEY1)+1,len(KEY1)) as KEYT
from TheDumbTable
where KEY1 like '%-%') inside
The result of that is something like...
-------------------------
|KEYFINT|KEYTINT|KEYTEXT|
-------------------------
| 10 | 30 | S |
| 19 | 40 | S |
-------------------------
If anyone has a good way to combine the two previous queries into one, I'd be grateful.
The big advantage to this example is that the TEXT part of the key can be anything, so this method will work with "ABC100" and "S10". The final trick is to wrap THAT query in yet another that has your WHERE...
WHERE cast(substring('THETHINGYOUWANT',patindex('%[0-9]%', 'THETHINGYOUWANT'),len('THETHINGYOUWANT')) as int)
BETWEEN UNITMATCHINGRANGES.keyintfrom AND UNITMATCHINGRANGES.keyintto
AND KEYTEXT LIKE replace('THETHINGYOUWANT',substring('THETHINGYOUWANT',patindex('%[0-9]%', 'THETHINGYOUWANT'),len('THETHINGYOUWANT')),''))
The first part pulls out the numeric part of the key you're looking for and BETWEENs that, but of course that means "A100" and "B100" would both match. So the second part pulls out the NON-numeric part and compares that to the non-numeric part of the key in the range.
This assumes that the range will always be something like "A100-A999", and not "A100-B100"
I suspect there are ways to dramatically simplify this into a one or two-step query, but for now it's working!
KEY1
,KEY2
andKEY3
? – Lamak Mar 21 '17 at 18:20