Split String with Multiple Delimiters (T-SQL XML Method Sample)



declare @myString varchar(100)='1-2,45,7#9-144'
 
;WITH cte
AS (
SELECT CAST(N'<H><r>' + Replace(Replace(replace(Replace(@myString,',','|'),'#','|'),'-','|'), '|', '</r><r>') + '</r></H>' as XML) AS vals 
)
 
SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS c1,
S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS c2,
S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS c3,
S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS c4,
S.a.value('(/H/r)[5]', 'NVARCHAR(50)') AS c5,
S.a.value('(/H/r)[6]', 'NVARCHAR(50)') AS c6,
S.a.value('(/H/r)[7]', 'NVARCHAR(50)') AS c7,
S.a.value('(/H/r)[8]', 'NVARCHAR(50)') AS c8

FROM cte CROSS APPLY vals.nodes('/H/r') S(a);


--From a table
declare @test table(id int identity(1,1), myString varchar(35) );
insert into @test values(@myString);

;with mycte as ( 
SELECT *,CAST(N'<H><r>' + Replace(Replace(replace(Replace(@myString,',','|'),'#','|'),'-','|'), '|', '</r><r>') + '</r></H>' as XML) AS vals
FROM @test

) 
 
SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS c1,
S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS c2,
S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS c3,
S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS c4,
S.a.value('(/H/r)[5]', 'NVARCHAR(50)') AS c5,
S.a.value('(/H/r)[6]', 'NVARCHAR(50)') AS c6,
S.a.value('(/H/r)[7]', 'NVARCHAR(50)') AS c7,
S.a.value('(/H/r)[8]', 'NVARCHAR(50)') AS c8
FROM mycte d
 
CROSS APPLY d.[vals].nodes('/H/r') S(a)

--Another in pair
 
declare @Questions varchar(100)='1-2,45,7#9-144'
 
DECLARE @myXML AS XML  = N'<H><r>' +Replace(@Questions, '#', '</r><r>') + '</r></H>'
 
;WITH cte
 
AS (
SELECT CAST(N'<H><r>' +  Replace(Vals.id.value('.', 'NVARCHAR(50)') ,'-' , '</r><r>') + '</r></H>' as XML) AS val
 
FROM @myXML.nodes('/H/r') AS Vals(id) )

,mycte1 as ( 
SELECT   S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS c1, CAST(N'<H><r>' +  Replace( S.a.value('(/H/r)[2]', 'NVARCHAR(50)')  , ',', '</r><r>') + '</r></H>' as XML) AS c2
FROM cte CROSS APPLY val.nodes('/H/r') S(a)
)
 

SELECT DISTINCT c1, S.a.value('.', 'NVARCHAR(50)') AS c2
FROM mycte1 CROSS APPLY  c2.nodes('/H/r') S(a);

/*
c1	c2
1	2
1	45
1	7
9	144

*/


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s