Create list of numbers from list of ranges

Question

I have a table with two columns that contains the start and end of a sequence of integers. It's a large list, but I'm pretty sure there are no overlaps in any of the ranges. I want to generate a list of all numbers contained within these range start and end bounds.

Basically I want to do the opposite of this question: How to create number ranges from a list of numbers?

From what I've read, my best guess at a solution might be some sort of dynamic cross apply, but I'm not really sure how to even begin that.

My table looks something like this:

Table

RangeStart RangeEnd
200        205
208        209
221        221
222        224

I want something like this:

Desired Result

And you want to use sql for this because? – Strawberry Jun 02 '15 at 16:25 — Strawberry, Jun 02 '15 at 16:25
@Strawberry If the only tool you have is a hammer... – Robert Jun 02 '15 at 17:04 — Robert, Jun 02 '15 at 17:04

Ed B · Accepted Answer · 2015-06-02T17:03:20.873

1

You can avoid using a 'numbers' table, by the use of a recursive common table expression:

WITH Numbers AS (
SELECT RangeStart, RangeStart AS Number, RangeEnd from RangeTable
UNION ALL
SELECT RangeStart, Number + 1, RangeEnd FROM Numbers WHERE Number < RangeEnd)
SELECT Number FROM Numbers ORDER BY Number

The first part is the 'anchor' query, which defines the root member (in this case, the range from number, the first number in the range, and the range to number).

The second part (after the UNION ALL) recursively joins to the anchor member and essentially keeps adding one to Number and recursing until it hits RangeEnd.

The final part gets just the numbers from the CTE we have built (we are no longer interested in the range start and end) and makes sure they are in the right order (this might not matter to you, in which case you can omit the ORDER BY clause.)

edit - if you are hitting a recursion limit with this, you can fix this by adding OPTION (MAXRECURSION 0) on the end of the query. Apologies for missing this!

edited Jun 02 '15 at 17:03

answered Jun 02 '15 at 16:47

Ed B

785
4
9

1

I like this one, but you might run into issues with max recursion... `Msg 530, Level 16, State 1, Line 13 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.` – Dave Carlile Jun 02 '15 at 16:59
I might add that using a CTE to generate number ranges in this way has several advantages, not least of which is the fact that it does not rely on the presence (or accessibility) of any 'number tables'. There is a good discussion of the various ways of generating ranges here: http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table – Ed B Jun 02 '15 at 16:59
@CrappyCodingGuy - you can easily circumvent the max recursion limit by adding the hint `OPTION (MAXRECURSION 0)` on the end of the query – Ed B Jun 02 '15 at 17:02
Thanks @EdB. I needed that fix for max recursion – Robert Jun 02 '15 at 17:06
Yes, but max recursion is there for a reason. If your ranges aren't large then it's probably not a problem. – Dave Carlile Jun 02 '15 at 18:14
Agreed, it is important to understand why max recursion is there. In this case, because the ranges are finite, it is acceptable to set it to 0 (i.e. infinite recursion). Even if the RangeStart and RangeEnd columns are nullable, because comparison with null always returns false, this won't recurse infinitely. It's probably advisable to set the max recursion to a sensible value whilst testing such a statement, in case you accidentally did something silly like putting `WHERE Number >= RangeStart` instead of `WHERE Number < RangeEnd`. – Ed B Jun 03 '15 at 08:26

score 0 · Answer 2 · answered Jun 02 '15 at 16:27

The easiest way is to start with a list of integers. A convenient one -- if the list is not too long -- is master..spt_values:

with n as (
      select row_number() over (order by (select null)) - 1 as n
      from master..spt_values
     )
select (rangestart + n.n) as sequence
from ranges r join
     n
     on r.rangestart + n.n <= r.rangeend
order by sequence;

If you are concerned about overlaps, then the following is an easy way to get the sequence:

select distinct (rangestart + n.n) as sequence
from ranges r join
     n
     on r.rangestart + n.n <= r.rangeend
order by sequence;

If the problem has lots of overlaps and values, then you would want a somewhat different approach (but that is not the question you are asking).

score 0 · Answer 3 · answered Jun 02 '15 at 16:28

declare @Sequence table(
  Value int
)

declare @Value int = 0

while (@Value < 500)
  begin
    insert @Sequence values(@Value)
    select @Value += 1
  end

select * from @Sequence

declare @Ranges table(
  RangeStart int,
  RangeEnd int
)

insert into @Ranges values(200, 205)
insert into @Ranges values(208, 209)
insert into @Ranges values(221, 221)
insert into @Ranges values(222, 224)


select s.Value
from @Sequence s
join @Ranges r on r.RangeStart <= s.Value and r.RangeEnd >= s.Value
order by s.Value

The key is getting your list of @Sequence values.

Create list of numbers from list of ranges

3 Answers3