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


http://forums.asp.net/p/1852168/5184467.aspx/1?p=True&t=634861565202699866

--declare @Questions varchar(100)='1-32|26,42-41|32'
declare @Questions varchar(100)='1-32-26,42-41-32,1-46-1'

DECLARE @myXML AS XML  = N'<H><r>' + Replace(@Questions, ',', '</r><r>') + '</r></H>'

;WITH cte

AS (
SELECT CAST(N'<H><r>' + replace(Replace(Vals.id.value('.', 'NVARCHAR(50)'),'-','|'), '|', '</r><r>') + '</r></H>' as XML) AS val

FROM @myXML.nodes('/H/r') AS Vals(id) )

SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS QuestionId,
S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS [IndexNumber],
S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS Numbers
FROM cte CROSS APPLY val.nodes('/H/r') S(a);

Another sample:


declare @test table(id int identity(1,1), myString varchar(35) );
insert into @test values('\string1\string2\file0123.xml.xxx');

SELECT distinct  S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS splitVal
FROM
(
SELECT *,CAST (N'<H><r>' + Replace(REPLACE(myString, '\', '</r><r>'),'.','</r><r>') + '</r></H>' AS XML) AS [vals]
FROM @test) d

CROSS APPLY d.[vals].nodes('/H/r') S(a)

---Or

declare @myString varchar(100)='\string1\string2\file0123.xml.xxx'

;WITH cte
AS (
SELECT CAST(N'<H><r>' + Replace(REPLACE(@myString, '\', '</r><r>'),'.','</r><r>') + '</r></H>' AS XML) AS val
 )

SELECT DISTINCT S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS val
FROM cte CROSS APPLY val.nodes('/H/r') S(a);

One more example:

create table test(id int,resp varchar(100))
insert into test(Id,resp)
SELECT 1,'1^123,2^321,3^234,4^321' UNION
SELECT 2,'1^321,2^123,3^345,4^543'
--select * from test

 ;with mycte as
 ( SELECT  id,  S.a.value('.', 'VARCHAR(100)') AS splitVal1 FROM( SELECT *,CAST (N'<H><r>' + Replace( resp, ',','</r><r>') + '</r></H>' AS XML) AS [vals]
 FROM test) d   CROSS APPLY d.[vals].nodes('/H/r') S(a)   
 )
 Select DISTINCT id, S.a.value('(/H/r)[1]', 'VARCHAR(100)') qn,  S.a.value('(/H/r)[2]', 'VARCHAR(100)') ans  
 FROM (Select *, CAST (N'<H><r>' + Replace( splitVal1, '^','</r><r>') + '</r></H>' AS XML) [vals] from mycte) d   
 CROSS APPLY d.[vals].nodes('/H/r') S(a)   
drop table test
/*
id	qn	ans
1	1	123
1	2	321
1	3	234
1	4	321
2	1	321
2	2	123
2	3	345
2	4	543
*/
Advertisements

8 Comments on “Split String with Multiple Delimiters (T-SQL XML Method)”

  1. kblanglois says:

    This is really a nice trick! I played with it last night to try and fit my need without success.

    My sequence if I take your example: ‘1-32-26,42-41-32,1-46-1’, would be 1, 42,1 not 1,32,26.

    I can’t seem to nail it down. Could you direct me in the right direction?

    • Jingyang Li says:

      Can you show your sample data to be processed? Thanks.

      • kblanglois says:

        Sure it’s a description of item(s) a single item would be @Name@description@Number@Id@. Two items would look like this @Name;Name2@description;Description2@Number;Number2@Id;Id2@. I never know how much item there will be.

        Would you have good litterature on xml and tsql to recommend?

        Thanks for the help.

      • Jingyang Li says:
        declare @s varchar(100)='@Name;Name2;Name3;Name4@description;Description2@Number;Number2@Id;Id2@-'
         
        DECLARE @myXML AS XML  = N'<H><r>' + Replace(@s, '@', '</r><r>') + '</r></H>'
         
        ;WITH cte
         
        AS (
        SELECT CAST(N'<H><r>' + replace(Replace(Vals.id.value('.', 'NVARCHAR(50)'),';','|'), '|', '</r><r>') + '</r></H>' as XML) AS val
         
        FROM @myXML.nodes('/H/r') AS Vals(id) )
         
         
        SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS N1,
        S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS N2,
        S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS N3,
        S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS N4,
        S.a.value('(/H/r)[5]', 'NVARCHAR(50)') AS N5
        FROM cte CROSS APPLY val.nodes('/H/r') S(a);
        
      • kblanglois says:

        Thx I duno why it wasn’t working for me last night, it seems pretty much like your example i’ll play again with it tonight.

        thx for your time

      • kblanglois says:

        Hi works fine if you have 3 items defined under that data is shown has:

        description Description2 NULL NULL NULL
        Id Id2 NULL NULL NULL
        Name Name2 NULL NULL NULL
        Number Number2 NULL NULL NULL

  2. kraaitje says:

    thanks, I am using this method to split a string with useraliases to multiple lines for my datawarehouse.

  3. Asma shaik says:

    If I have a sample data like ‘DRYabdfgPAXgfjr123DRY’….I need to split into rows base on ‘DRY’ and ‘PAX’….can u please suggest


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