CODEWITHSUNDEEP
sql-serverxml
Sam
Sam

Reputation: 61

Split String using XML in SQL Server

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)

Upvotes: 0

Views: 4909

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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.

Upvotes: 1

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

    --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

Upvotes: 1

Related Questions