Split String using XML in SQL Server

Question

Question: how to split below string using XML?

Input:

'7-VPN Connectivity 7.8 - Ready to Elixir Connector install 9-Unified Installation'

Expected output:

7-VPN Connectivity  
7.8 - Ready to Elixir Connector install  
9-Unified Installation

My code:

DECLARE @xml AS XML,  
        @str AS VARCHAR(100)  

SET @str = '7-VPN Connectivity 7.8 - Ready to Elixir Connector install 9-Unified Installation'  

SET @xml = CAST(('<X>'+replace(@str,' ','</X><X>')+'</X>') AS XML)  

SELECT 
    N.value('.', 'VARCHAR(10)') AS value 
FROM 
    @xml.nodes('X') AS T(N)

Alfaiz Ahmed · Accepted Answer · 2017-07-14 06:57:25Z

2

    --Provide the comma From Where you wan't to split The Data
   -- For Eg:

    BEGIN TRAN


        DECLARE @S varchar(max),
            @Split char(1),
            @X xml

    SELECT @S = '7-VPN Connectivity ,7.8- Ready to Elixir Connector install, 9-Unified Installation',
           @Split = ','

    SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@S,@Split,'</s> <s>') + '</s>   </root> ')

    SELECT [Value] = T.c.value('.','varchar(255)')
    FROM @X.nodes('/root/s') T(c)

    ROLLBACK TRAN

answered Jul 14, 2017 at 6:57

Alfaiz Ahmed

1,7261 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gottfried Lesigang · Accepted Answer · 2017-07-14 21:40:55Z

This is a horrible design! If there is the slightest chance to fix this you should change this the sooner the better...

You might try something like this, but use it only to clean up that mess!

DECLARE @YourString VARCHAR(100)='7-VPN Connectivity 7.8 - Ready to Elixir Connector install 9-Unified Installation';

WITH CutAtHyphen(Nr,part) AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
           ,LTRIM(RTRIM(A.part.value('text()[1]','nvarchar(max)'))) 
    FROM
    (
        SELECT CAST('<x>' + REPLACE((SELECT @YourString AS [*] FOR XML PATH('')),'-','</x><x>') + '</x>' AS XML) AS Casted
    ) AS t
    CROSS APPLY t.Casted.nodes('/x') AS A(part)
)
,CutOffFinal AS
(
    SELECT Nr
          ,part
          ,LEFT(part,LEN(part)-PositionOf.LastBlank) AS Remainder
          ,CASE WHEN Nr>1 THEN RIGHT(part,PositionOf.LastBlank) ELSE part END AS Tail
    FROM CutAtHyphen
    OUTER APPLY (SELECT CHARINDEX(' ',REVERSE(part))) AS PositionOf(LastBlank)
)
,recCTE AS
(
    SELECT Nr, CAST(N'' AS NVARCHAR(MAX)) AS String,Tail FROM CutOffFinal WHERE Nr=1
    UNION ALL
    SELECT cof.Nr
          ,r.Tail + '-' + cof.Remainder
          ,cof.Tail
    FROM recCTE AS r
    INNER JOIN CutOffFinal AS cof ON cof.Nr=r.Nr+1

)
SELECT String + CASE WHEN Nr=(SELECT MAX(Nr) FROM CutOffFinal) THEN Tail ELSE '' END AS FinalString
FROM recCTE
WHERE Nr>1;

This code will first of all cut the string at the hyphens and trim it. The it will search for the last blank and cut of the number, which belongs to the next row.

The recursive CTE will travel down the line and concatenate the tail of the previous row, with the remainder of the current.

The first and the last line need special treatment.

Collectives™ on Stack Overflow

Split String using XML in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related