Split string into table with rows (multiple delimeters)

Question

I need in SQL Server to split the following string:

'1:1:0:0:1231,1:0:0:0101:1231'

First I need to split into 2 rows:

1:1:0:0:1231
1:0:0:0101:1231

And finally I have to split each row in separate columns with the following name:

isactive--|--year--|--anniversay--|--startperiod--|--endperiod
   1          1           0               0            1231
   1          0           0              0101          1231

I have the following function for split but it just work for the first split (,) because it doesnt work for the columns.

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

Any clue?

Is this an ongoing function you need to create, or a one time fix to clean dirty data? — Twelfth, Aug 11 '14 at 22:22
This is an ongoing function, it can be a function or just the query because it will only be used in one stored procedure. — VAAA, Aug 11 '14 at 22:24
possible duplicate of [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Cᴏʀʏ, Aug 11 '14 at 22:24

score 1 · Accepted Answer · answered Aug 11 '14 at 22:25

How about applying the function twice? The second time you need to re-aggregate the values into a new row. Something like:

select r.*
from t cross apply
     (select data
      from split(t.col, ';')
     ) di cross apply
     (select max(case when did.id = 1 then did.data end) as isactive,
             max(case when did.id = 2 then did.data end) as year,
             max(case when did.id = 3 then did.data end) as anniversary,
             max(case when did.id = 4 then did.data end) as startperiod,
             max(case when did.id = 5 then did.data end) as endperiod
      from split(di.data) did
     ) r;

Split string into table with rows (multiple delimeters)

1 Answers1