Analisar uma coluna com um "intervalo" em um WHERE no SQL Server

Tudo bem, por favor, não me odeie por esta consulta, porque tenho certeza de que existem maneiras muito melhores de fazer isso (começando corrigindo esse design). Com os dados de exemplo que você postou, essa consulta deve fornecer todas as chaves da tabela original como uma única coluna ( 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
;

Há um par de suposições embora.

• O único tipo de intervalo que você pode ter é este tipo: s001-s100, você não pode ter, s001-s100,s005-s006 por exemplo
• Cada chave tem um comprimento de 4 caracteres, começando com s001atés999

Se você tiver uma tabela de números, poderá analisar as duas partes do intervalo e juntar-se à sua tabela de números. Então você só precisa formatá-lo de volta para como sua chave de junção está formatada.

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

demonstração do rextester : http://rextester.com/KAJ77714

retorna:

+-----+-----------+---------+---------+---------+
|     |   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    |
+-----+-----------+---------+---------+---------+

Referência da tabela de números:

• Gerar um conjunto ou sequência sem loops - 1 - Aaron Bertrand
• Gerar um conjunto ou sequência sem loops - 2 - Aaron Bertrand
• Gerar um conjunto ou sequência sem loops - 3 - Aaron Bertrand
• A tabela "Numbers" ou "Tally": o que é e como substitui um loop - Jeff Moden

T-SQL - 246 Bytes

Rapido e sujo:

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 |
-----------

Com a ressalva de que o parsename não vai além de 4 níveis

E eu adiciono mais um...

Combinando parte do código acima junto com alguma manipulação básica de strings, o que fiz foi fazer uma consulta interna que retornou ambos os lados do sinal de menos em dois campos...

select Substring(KEY1,CharIndex('-',KEY1)) as KEYF,
Substring(KEY1,CharIndex('-',KEY1)+1,len(KEY1)) as KEYT
from TheDumbTable
where KEY1 like '%-%'

Isso retorna algo como...

-------------
|KEYF |KEYT |
-------------
| S10 | S30 |
| S19 | S40 |
-------------

Em seguida, envolvo isso em outra consulta que usa PATINDEX para separar os números dos numéricos ...

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

O resultado disso é algo como...

-------------------------
|KEYFINT|KEYTINT|KEYTEXT|
-------------------------
| 10    | 30    | S     |
| 19    | 40    | S     |
-------------------------

Se alguém tiver uma boa maneira de combinar as duas consultas anteriores em uma, ficaria grato.

A grande vantagem deste exemplo é que a parte TEXT da chave pode ser qualquer coisa, então este método funcionará com "ABC100" e "S10". O truque final é envolver essa consulta em outra que tenha seu 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')),''))

A primeira parte puxa a parte numérica da chave que você está procurando e ENTRE isso, mas é claro que isso significa que "A100" e "B100" combinariam. Assim, a segunda parte extrai a parte NÃO numérica e a compara com a parte não numérica da chave no intervalo.

Isso pressupõe que o intervalo sempre será algo como "A100-A999" e não "A100-B100"

Suspeito que existam maneiras de simplificar drasticamente isso em uma consulta de uma ou duas etapas, mas por enquanto está funcionando!