Split Data In Sql With Multiple Delimiters ( , And - ) With Owns Rule

I have data like this 1,2,3,4-8,10,11 I want split the data into rows with these 2 rules : The , will only split the data into rows. Ex 1,2,3 become : 1 2 3 The - will split into

Solution 1:

This will work as long as your intervals are less than 2048 (let me know if that numbers can go higher) and you @data follow your current syntax:

declare@datavarchar(50) ='1,2,3,4-8,10,11'

;with x as
(
     SELECT t.c.value('.', 'VARCHAR(2000)') subrow
     FROM (
         SELECT x =CAST('<t>'+ 
               REPLACE(@data, ',', '</t><t>') +'</t>'AS XML)
     ) a
     CROSS APPLY x.nodes('/t') t(c)
), y as
(
SELECTCAST(coalesce(PARSENAME(REPLACE(subrow, '-', '.'), 2),
              PARSENAME(REPLACE(subrow, '-', '.'), 1)) asint) f,
CAST(PARSENAME(REPLACE(subrow, '-', '.'), 1) asint) t from x
)
select z.number from y
cross apply 
(select y.f + number number
from master..spt_values
where number <= y.t - y.f and type ='p'
) z

Result:

1
2
3
4
5
6
7
8
10
11

Solution 2:

CREATEFUNCTIONdbo.MultipleDelemiterSplit
(
   @List       NVARCHAR(MAX),
   @Delemiter1 Varchar(100),
   @Delemiter2 Varchar(100)
)
RETURNSTABLEASRETURN
   ( 
      SELECT Item = FirstSet.cnt.value('(./text())[1]', 'nvarchar(4000)')
      FROM
      (
        SELECT x = CONVERT(XML, '<cnt>'
          + REPLACE(REPLACE(@List, ISNULL(@Delemiter1,''), '</cnt><cnt>') , ISNULL(@Delemiter2,''), '</cnt><cnt>')
          + '</cnt>').query('.')
      ) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)
   );

GOSelect * Fromdbo.MultipleDelemiterSplit ('10:0,11:1,12:3,13:4,15:5,16:6',',',':')

Solution 3:

I just created a sample for two delimiters, need to do something for generic

CREATEFUNCTIONdbo.SplitStrings
(
   @List       NVARCHAR(MAX),
   @Separator1 Varchar(100),
   @Separator2 Varchar(100)
)
RETURNSTABLEASRETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(REPLACE(@List, ISNULL(@Separator1,''), '</i><i>') , ISNULL(@Separator2,''), '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

GOSelect * Fromdbo.SplitStrings ('1,2,3,4 5,6,7',',','-')

Just Pass null or empty if second separator is not required

Solution 4:

For the first part of the question you could use a recursive CTE:

select'1,2,3,4-8,10,11'asvalueinto #testtab

with tst(DelimValue,Value) as
(
    selectcast(LEFT(value,charindex(',',value+',')-1) asvarchar(50)),stuff(value,1,charindex(',',value+','),'') 
    from #testtab
     unionallselectcast(LEFT(value,charindex(',',value+',')-1) asvarchar(50)),stuff(value,1,charindex(',',value+','),'')
    from tst
    wherevalue>''
)
select DelimValue 
from tst

This will give you the results:

1
2
3
4-8
10
11

To give credit where it is due, I learned this from here: Turning a Comma Separated string into individual rows.

Now for the ranges, I would imagine you could write a procedure with a loop that will find the first integer before the dash as the start, and the second integer as the end, and then insert / return rows for that range. Though I imagine there must be a smarter way of doing this.