CODEWITHSUNDEEP
sql-serverrangesql-like
spot
spot

Reputation: 5

How to query number based SQL Sets with Ranges in SQL

What I'm looking for is a way in MSSQL to create a complex IN or LIKE clause that contains a SET of values, some of which will be ranges.

Sort of like this, there are some single numbers, but also some ranges of numbers.

EX: SELECT * FROM table WHERE field LIKE/IN '1-10, 13, 24, 51-60'

I need to find a way to do this WITHOUT having to specify every number in the ranges separately AND without having to say "field LIKE blah OR field BETWEEN blah AND blah OR field LIKE blah. This is just a simple example but the real query will have many groups and large ranges in it so all the OR's will not work.

Upvotes: 0

Views: 1542

Answers (2)

Bert Wagner
Bert Wagner

Reputation: 881

You can do this with CTEs.

First, create a numbers/tally table if you don't already have one (it might be better to make it permanent instead of temporary if you are going to use it a lot):

;WITH Numbers AS
(
    SELECT
        1 as Value
    UNION ALL
    SELECT
        Numbers.Value + 1
    FROM
        Numbers
)
SELECT TOP 1000
    Value
INTO ##Numbers
FROM
    Numbers
OPTION (MAXRECURSION 1000)

Then you can use a CTE to parse the comma delimited string and join the ranges with the numbers table to get the "NewValue" column which contains the whole list of numbers you are looking for:

DECLARE @TestData varchar(50) = '1-10,13,24,51-60'

;WITH CTE AS 
(
    SELECT
        1 AS RowCounter,
        1 AS StartPosition,
        CHARINDEX(',',@TestData) AS EndPosition

    UNION ALL
    SELECT
        CTE.RowCounter + 1,
        EndPosition + 1,
        CHARINDEX(',',@TestData, CTE.EndPosition+1)
    FROM CTE
    WHERE
        CTE.EndPosition > 0
)

SELECT
    u.Value,
    u.StartValue,
    u.EndValue,
    n.Value as NewValue
FROM
    (
    SELECT
        Value,
        SUBSTRING(Value,1,CASE WHEN CHARINDEX('-',Value) > 0 THEN CHARINDEX('-',Value)-1 ELSE LEN(Value) END) AS StartValue,
        SUBSTRING(Value,CASE WHEN CHARINDEX('-',Value) > 0 THEN CHARINDEX('-',Value)+1 ELSE 1 END,LEN(Value)- CHARINDEX('-',Value))  AS EndValue
    FROM
        (
        SELECT
            SUBSTRING(@TestData, StartPosition, CASE WHEN EndPosition > 0 THEN EndPosition-StartPosition ELSE LEN(@TestData)-StartPosition+1 END) AS Value
        FROM 
            CTE
        )t
    )u INNER JOIN ##Numbers n ON n.Value BETWEEN u.StartValue AND u.EndValue

All you would need to do once you have that is query the results using an IN statement, so something like 

SELECT * FROM MyTable WHERE Value IN (SELECT NewValue FROM (/*subquery from above*/)t)

Upvotes: 0

JNK
JNK

Reputation: 65187

One fairly easy way to do this would be to load a temp table with your values/ranges:

CREATE TABLE #Ranges (ValA int, ValB int)

INSERT INTO #Ranges
VALUES
 (1, 10)
,(13, NULL)
,(24, NULL)
,(51,60)

SELECT *
FROM Table t
JOIN #Ranges R
ON (t.Field = R.ValA AND R.ValB IS NULL)
   OR (t.Field BETWEEN R.ValA and R.ValB AND R.ValB IS NOT NULL)

The BETWEEN won't scale that well, though, so you may want to consider expanding this to include all values and eliminating ranges.

Upvotes: 1

Related Questions