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

See also


Learn more tricks

Add Comment

Comments