Alternate String Splitter (XML)

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 11 Mar 2011

Description

This is a novel way of converting (pivoting) a delimited string into a table. I'm not sure it would be very performant, and may be an example of what can be done rather than what should be done but then I've been badly beaten by developer's inadvisable/inappropriate use of shiny new XML features in the past, so I'm probably biased.

Anyway, this makes use of the XML Nodes function to pivot the delimited string into a table. In this case, a multiple character delimiter is possible.

Code

Function:

DROP FUNCTION dbo.udf_split3

GO

CREATE FUNCTION dbo.udf_split3

(

@Parameter VARCHAR(8000),

@Delimiter VARCHAR(10)

)

RETURNS @Results TABLE

(

ID INT IDENTITY(1,1), -- to maintain the same order as the original string

Value VARCHAR(8000)

)

AS

/**********************************************************

Purpose: An alternate method of splitting a delimited string

This one uses XML - an artificial XML tag is used

to replace the delimiter.

Author: Unknown

History 15 Mar 2011

16 Jul 2012 - ChillyDBA - Converted to function

***********************************************************/

BEGIN

DECLARE @t TABLE(data VARCHAR(8000))

INSERT INTO @t(data) SELECT @Parameter

INSERT @Results (Value)

SELECT

O.splitdata

FROM

(

SELECT

*,

CAST('<X>'+REPLACE(F.data,@Delimiter,'</X><X>')+'</X>' AS XML) AS xmlfilter

FROM @t F

) F1

CROSS APPLY

(

SELECT

fdata.D.value('.','varchar(50)') AS splitdata

FROM f1.xmlfilter.nodes('X') AS fdata(D)) O

RETURN

END

Test Code:

SELECT * FROM dbo.udf_split3('AA,AB,AC,AD', ',')

SELECT * FROM dbo.udf_split3('BA^BB^BC', '^')

SELECT * FROM dbo.udf_split3('BA^$^BB^$^BC', '^$^')