Generate Numbers between Range

Question

Suppose I have 2-6 and by using the below program I am able to generate the range

DECLARE @range VARCHAR(10) = '2-6'
DECLARE @startRange INT = PARSENAME(REPLACE(@range, '-', '.'), 2)
DECLARE @lastRange INT = PARSENAME(REPLACE(@range, '-', '.'), 1)

SELECT DISTINCT Number = number
FROM master..spt_values
WHERE number BETWEEN @startRange AND @lastRange

Now I have the input as 2-6,9-12,15-20

I know that by splitting the record with , as seperator and using the above query as function I can achieve the target. However, I would like to use CROSS APPLY for it to avoid function.

I have tried as under

DECLARE @range VARCHAR(50) = '2-6,9-12,15-20'

SELECT
    value   
FROM STRING_SPLIT(@range,',')

But how to use CROSS APPLY with the above query with master..spt_values?

GarethD · Accepted Answer · 2017-02-15T09:01:17.690

If you simply extract the start and end position of each item, for which you could use something like:

DECLARE @Value VARCHAR(20) = '2-6';

SELECT  Start = TRY_CONVERT(INT, LEFT(@Value, CHARINDEX('-', @Value) - 1)),
        [End] = TRY_CONVERT(INT, SUBSTRING(@Value, CHARINDEX('-', @Value) + 1, LEN(@Value)));

Which gives:

Start   End
------------
2       6

Then you can simply join to master..spt_values:

DECLARE @range VARCHAR(50) = '2-6,9-12,15-20'

SELECT  s.Value, v.Number
FROM    STRING_SPLIT(@range,',') AS s
        INNER JOIN master..spt_values AS v
            ON v.Number >= TRY_CONVERT(INT, LEFT(s.Value, CHARINDEX('-', s.Value) - 1))
            AND v.Number <= TRY_CONVERT(INT, SUBSTRING(s.Value, CHARINDEX('-', s.Value) + 1, LEN(s.Value)))
            AND v.Type = 'P';

For what it is worth master..spt_values is fairly limited, there are better ways to generate a set or sequence without a loop.

When using master..spt_values the range 2500-2501 wouldn't work, but if you had a numbers table, or a different source of numbers it would be fine, e.g. the following works up to 10,000, then by uncommenting further lines you can increase the range:

DECLARE @range VARCHAR(50) = '2-6,9-12,15-20,2500-2501';

WITH Numbers (Number) AS
(   SELECT  ROW_NUMBER() OVER(ORDER BY N1.N)
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 (N)         -- 100
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N2 (N)   -- 100
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N3 (N)   -- 1,000
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N4 (N)   -- 10,000
    --CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N5 (N) -- 100,000
)
SELECT  s.Value, n.Number
FROM    STRING_SPLIT(@range,',') AS s
        INNER JOIN Numbers AS n
            ON n.Number >= TRY_CONVERT(INT, LEFT(s.Value, CHARINDEX('-', s.Value) - 1))
            AND n.Number <= TRY_CONVERT(INT, SUBSTRING(s.Value, CHARINDEX('-', s.Value) + 1, LEN(s.Value)))
ORDER BY n.Number;

score 1 · Answer 2 · answered Feb 15 '17 at 08:38

You can achieve the result using the below script

SELECT DISTINCT Number = number
FROM master..spt_values
    CROSS APPLY STRING_SPLIT(@range,',') AS S
WHERE number BETWEEN PARSENAME(REPLACE(value, '-', '.'), 2) 
                AND PARSENAME(REPLACE(value, '-', '.'), 1)

score 1 · Answer 3 · answered Feb 15 '17 at 08:48

Cross apply version

SELECT t.number     
FROM STRING_SPLIT(@range,',') sv
CROSS APPLY (
    SELECT startv = CAST(PARSENAME(REPLACE(value, '-', '.'), 2) AS INT),
           endv = CAST(PARSENAME(REPLACE(value, '-', '.'), 1) AS INT)             
) param
CROSS APPLY (
   SELECT TOP(endv-startv+1) number = startv + row_number() over(order by startv) - 1
   FROM master..spt_values
) t;

Generate Numbers between Range

3 Answers3