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', '^$^')