CODEWITHSUNDEEP
sqlsql-server
Robert
Robert

Reputation: 784

Create list of numbers from list of ranges

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

Sequence
200
201
202
203
204
205
208
209
221
222
223
224

Upvotes: 1

Views: 1492

Answers (3)

Ed B
Ed B

Reputation: 796

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!

Upvotes: 1

Dave Carlile
Dave Carlile

Reputation: 7457

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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).

Upvotes: 0

Related Questions