Parse a column with a "range" in a WHERE in SQL Server

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?

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

StackExchange Database Administrators Q#167778, answer score: 3