Split String in SQL SERVER Using XML

Split delimited string using XML.

Hello readers....

Below is a simple example to split string by using XML. Create a function that does the task. This function accepts two parameters. First is the string with delimiters e.g.(a,b,c,d – here , are the delimiters)  and second parameter is separator or delimiter (in example , is delimiter). You can use any character for delimiter instead of a comma. I am using coma as a delimiter in the current example.

First we convert the string in to XML by using CAST and then by querying over the generated XML we generate the columns that hold the split output of the string.

Below is the code.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION fnSplit

(

      @string VARCHAR(MAX),

      @seprator VARCHAR(1)   

)

RETURNS @chunks TABLE (Chunks VARCHAR(200))

AS

BEGIN

      SET @string = LTRIM(RTRIM(REPLACE(@string, @seprator, ' ')))

      DECLARE @xml XML = (SELECT CAST('<x>'+ REPLACE(@string, ' ', '</x><x>') + '</x>' AS XML))

      INSERT INTO @chunks

      SELECT x.value('.','VARCHAR(100)') AS a FROM @xml.nodes('x') f(x) WHERE LEN(LTRIM(RTRIM(x.value('.','VARCHAR(100)')))) > 0   

     

      RETURN

END

GO

Execute the function and close the query window.

Now open new query window and execute the function like below

SELECT * FROM dbo.fnSplit('aa|bb|cc|dd|ee|ff', '|') -- here | is delimiter

SELECT * FROM dbo.fnSplit('aa,bb,cc,dd,ee,ff', ',') -- here , is delimiter

SELECT * FROM dbo.fnSplit('aa:bb:cc:dd:ee:ff', ':') -- here : is delimiter

SELECT * FROM dbo.fnSplit('aa;bb;cc;dd;ee;ff', ';') -- here ; is delimiter