Split data in sql with multiple delimiters ( , and - ) with owns rule

Question

I have data like this 1,2,3,4-8,10,11

I want split the data into rows with these 2 rules :

1. The , will only split the data into rows. Ex 1,2,3 become :

   1
   2
   3
   

2. The - will split into series number. Ex 4-8 become :

   4
   5
   6
   7
   8 
   

How can a SQL query do that? Please answer and keep it simple.

Answer 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 @data varchar(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
(
SELECT 
CAST(coalesce(PARSENAME(REPLACE(subrow, '-', '.'), 2),
              PARSENAME(REPLACE(subrow, '-', '.'), 1)) as int) f,
CAST(PARSENAME(REPLACE(subrow, '-', '.'), 1) as int) 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

Answer 2

CREATE FUNCTION dbo.MultipleDelemiterSplit
(
   @List       NVARCHAR(MAX),
   @Delemiter1 Varchar(100),
   @Delemiter2 Varchar(100)
)
RETURNS  TABLE
AS

   RETURN
   ( 
      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)
   );

GO 

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