Parse a column with a "range" in a WHERE in SQL Server
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