Parse a column with a “range” in a WHERE in SQL Server

Question

I'm querying a table that has several alphanumeric key columns...

KEY1  KEY2  KEY3 ... SOMEDATA  SOMEOTHERDATA...
s001
s002
s003  s004  s005
s006

The keys are the PK from another table we need to join on, which is annoying but not difficult. But then they decided to do this...

s010-s105

Which means s010, s011...s015. Is there any way to do a query that breaks this out so I can find a given key, say s013, in these columns?

Answer 1

T-SQL - 246 Bytes

Quick and dirty:

create table #DataInRanges (KeyId varchar(10), FirstName varchar(10), LastName varchar(10));

insert into #DataInRanges (KeyId, FirstName, LastName) values
('s010-s015', 'Nelson', 'Casanova'),
('s009-s010', 'Karl', 'Johnson'),
('s090-s110', 'Michael', 'Buxton'),
('s079-s080', 'Terrence', 'Dickson'),
('s010-s030', 'Mario', 'Carrizo'),
('s025-s085', 'Victor', 'Lee'),
('s019-s040', 'Jim', 'Buckney'),
('s030-s110', 'Solomon', 'Bennett')

declare @ANumber INT = 20;

select * 
from
(
    select
        cast(replace(parsename(replace(KeyId, '-','.'),2), 's', '') as int) as Key1, 
        cast(replace(parsename(replace(KeyId, '-','.'),1), 's', '') as int) as Key2
    from #DataInRanges
) t
where (@ANumber) between t.Key1 and t.Key2

-----------
|key1|key2|
-----------
| 10 | 30 |
| 19 | 40 |
-----------

With the caveat that parsename wont dig beyond 4 levels

Answer 2

Alright, please don't hate me for this query, because I'm sure there are far better ways to do this (starting by fixing this design). With the sample data that you posted, this query should give you every key in the original table as a single column (KEY_Unified):

WITH CTE AS
(
    SELECT *
    FROM dbo.YourTable t
    CROSS APPLY 
    (
        VALUES
            (t.KEY1),
            (t.KEY2),
            (t.KEY3)
    ) x (KEY_Unified)
    WHERE x.KEY_Unified IS NOT NULL
), CTE2 AS
(
    SELECT  KEY_Unified, 
            CAST(RIGHT(LEFT(KEY_Unified,CHARINDEX('-',KEY_Unified,0)-1),3) AS INT) Start,
            CAST(RIGHT(SUBSTRING(KEY_Unified,CHARINDEX('-',KEY_Unified,0)+1,4),3) AS INT) Finish
    FROM CTE 
    WHERE KEY_Unified LIKE '%-%'
), CTE3 AS 
(
    SELECT 's' + RIGHT('000' + CAST(B.number AS VARCHAR(3)),3) KEY_Range, CAST(B.number AS VARCHAR(3)) n
    FROM CTE2 A
    CROSS JOIN (SELECT *
                FROM master.dbo.spt_values
                WHERE type = 'P') B
    WHERE B.number BETWEEN A.Start AND A.Finish
), CTE4 AS
(
    SELECT KEY_Unified
    FROM CTE
    WHERE KEY_Unified NOT LIKE '%-%'
    UNION
    SELECT KEY_Range
    FROM CTE3
)
SELECT *
FROM CTE4 t1
LEFT JOIN SomeOtherTable t2
    ON t1.KEY_Unified = t2.SomeKey
;

There are a couple of assumptions though.

• The only kind of range you can have is this kind: s001-s100, you can't have s001-s100,s005-s006 for instance
• Every key has a 4 char length, starting with s001 till s999

Answer 3

If you have an a number tables, you can parse out the two pieces of the range and join on your numbers table. Then you just have to format it back to how your join key is formatted.

create table t (key1 varchar(10),key2 varchar(10),key3 varchar(10));
insert into t values ('s001',null,null),('s010-s105',null,null),('s002','s003','s004');
/* numbers table substitute for demo*/
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, numbers as (
  select n = row_number() over(order by (select 1))-1
  from n as deka cross join n as hecto cross join n as kilo 
                 cross join n as tenK cross join n as hundredK
)
select 
    t.key1
  , x.*
  , joinKey = left(key1,1)+right('000'+convert(varchar(10),n.n),3)
from t 
  cross apply (values (key1),(key2),(key3)
    ) as u (keyN)
  cross apply (values (stuff(left(u.keyN,charindex('-',u.keyN+'-')-1),1,1,'')
                      ,stuff(u.keyN,1,charindex('-',u.keyN)+1,''))
    ) as x (fromkey,thrukey)
  left join numbers n
    on n >= try_convert(int,fromkey)
   and n <= try_convert(int,thrukey)
where u.keyN is not null

rextester demo: http://rextester.com/KAJ77714

returns:

+-----+-----------+---------+---------+---------+
|     |   key1    | fromkey | thrukey | joinKey |
+-----+-----------+---------+---------+---------+
|   1 | s001      |     001 |     001 | s001    |
|   2 | s002      |     002 |     002 | s002    |
|   3 | s002      |     003 |     003 | s003    |
|   4 | s002      |     004 |     004 | s004    |
|   5 | s010-s105 |     010 |     105 | s010    |
|   6 | s010-s105 |     010 |     105 | s011    |
|   7 | s010-s105 |     010 |     105 | s012    |
|   8 | s010-s105 |     010 |     105 | s013    |
|   9 | s010-s105 |     010 |     105 | s014    |
...
|  97 | s010-s105 |     010 |     105 | s102    |
|  98 | s010-s105 |     010 |     105 | s103    |
|  99 | s010-s105 |     010 |     105 | s104    |
| 100 | s010-s105 |     010 |     105 | s105    |
+-----+-----------+---------+---------+---------+

Numbers table reference:

• Generate a set or sequence without loops - 1 - Aaron Bertrand
• Generate a set or sequence without loops - 2 - Aaron Bertrand
• Generate a set or sequence without loops - 3 - Aaron Bertrand
• The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden

Answer 4

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!