RA19
RA19

Reputation: 809

SQL Splitting a varchar range into individual numbers

If I have a range in a column e.g.

1-5,6,7

I want to output:

1
2
3
4
5
6
7

Is this possible in SQL? I am using SQL server management studio.

Upvotes: 0

Views: 76

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

First we Split your string and create R1 and R2, and then we apply an ad-hoc tally table to fill-in the blanks

Declare @YourTable table (ID int,RangeString varchar(max))
Insert Into @YourTable values
(1,'1-5,6,7'),
(2,'25000-25010,10-15')


Select A.ID
      ,C.N
 From @YourTable A
 Cross Apply (
       Select R1=cast(left(RetVal,charindex('-',RetVal+'-')-1) as int)
             ,R2=cast(case when charindex('-',RetVal)=0 then left(RetVal,charindex('-',RetVal+'-')-1) else replace(RetVal,left(RetVal,charindex('-',RetVal+'-')),'') end  as int)
        From (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(RangeString,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
                Cross Apply x.nodes('x') AS B(i)
             ) P
      ) B
 Cross Apply ( 
              Select Top (B.R2-B.R1+1) N=Row_Number() Over (Order By (Select null))+B.R1-1 From master..spt_values N1,master..spt_values N2
             ) C

Returns

ID  N
1   1
1   2
1   3
1   4
1   5
1   6
1   7
2   25000
2   25001
2   25002
2   25003
2   25004
2   25005
2   25006
2   25007
2   25008
2   25009
2   25010
2   10
2   11
2   12
2   13
2   14
2   15

Edit - Cleaner Version with a UDF

Select A.ID
      ,D.N
 From @YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.RangeString,',') B
 Cross Apply (
              Select R1=min(cast(RetVal as int))
                    ,R2=max(cast(RetVal as int))
               From [dbo].[udf-Str-Parse](B.RetVal,'-') 
              ) C
 Cross Apply ( 
                Select Top (C.R2-C.R1+1) N=Row_Number() Over (Order By (Select null))+C.R1-1 
                  From master..spt_values N1,master..spt_values N2
             ) D

The UDF if interested

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')
--Performance On a 5,000 random sample -8K 77.8ms, -1M 79ms (+1.16), -- 91.66ms (+13.8)

Upvotes: 1

Related Questions