MS SQL Tips & Tricks

How to parse a ranges of numbers into a table

Problem of parsing ranges of numbers int a table is similar to then converting comma-delimited string to the table of items. We just need to do some improvements. Let's say that the string we want to parse is '1,2,33,44,22-33, 11-17'. At first we do the same thing as before - build a table of items that we will parse in the next step.

create table #TEMP
( 
 ID bigint identity (1,1) not null,	
 Item varchar(50) default '',
 Range_From int default 0,	
 Range_To int default 0,
 flgRange tinyint default 0
)

Declare @cValues as varchar(max); 
set @cValues='1,2,33,44,22-33, 11-17';

declare @cXML XML;
set @cXML=cast('<a>'+
		REPLACE(@cValues, ',' ,'</a><a>')+'</a>' 
		as XML);

insert into #TEMP(Item)
SELECT nref.value('.','nvarchar(50)') as Item
from @cXML.nodes('/a') AS R(nref)

In the result we get:

ID Item Range_From Range_To flgRange
1 1 0 0 0
2 2 0 0 0
3 33 0 0 0
4 44 0 0 0
5 22-33 0 0 0
6 11-17 0 0 0

Now is the time to parse ranges and update temporary table:

update #TEMP
set 
	flgRange=1,
	Range_From=pom.Range_From,
	Range_To=pom.Range_To
from(
	select Id, substring(Item,1,
		CHARINDEX('-', Item)-1
			) as Range_From,
	  substring(Item,
			CHARINDEX('-', Item)+1, 
				len(Item)-(CHARINDEX('-', Item))
			) as Range_To	
	from #TEMP
	where Item like '%-%'
) as pom
where pom.Id=#TEMP.Id

ID Item Range_From Range_To flgRange
1 1 0 0 0
2 2 0 0 0
3 33 0 0 0
4 44 0 0 0
5 22-33 22 33 1
6 11-17 11 17 1

The last thing to do is to fill up table with numbers of given ranges...

insert into #TEMP(Item)
select p1.Digit+10*p2.Digit+100*p3.Digit
from #TEMP t
inner join (select cast(0 as int) as Digit
			union select 1 union select 2
			union select 3 union select 4
			union select 5 union select 6
			union select 7 union select 8
			union select 9) as p1 on (1=1)
inner join (select cast(0 as int) as Digit
			union select 1 union select 2
			union select 3 union select 4
			union select 5 union select 6
			union select 7 union select 8
			union select 9) as p2 on (1=1)
inner join (select cast(0 as int) as Digit
			union select 1 union select 2
			union select 3 union select 4
			union select 5 union select 6
			union select 7 union select 8
			union select 9) as p3 on (1=1)
where t.flgRange=1 and (
	t.Range_From<=(p1.Digit+10*p2.Digit+100*p3.Digit)and
	t.Range_To>=(p1.Digit+10*p2.Digit+100*p3.Digit)
)
order by t.Item,p1.Digit+10*p2.Digit+100*p3.Digit

... and remove range items just before getting the result. We are done here.

delete from #TEMP where flgRange=1;

select distinct Item from #TEMP order by Item

Below you can find a link to download all above defined as a single, ready to use function. This sollution will work properly with ranges 0-999. If you need more, then just add subsequent join sections with unions in the filling up step.

Download SQL script

Learn more tricks

Add Comment

Comments

25th May 2018

That is awesome Jacek! Works like a charm!

DeWayne