Cool Tips‎ > ‎String Manipulation‎ > ‎

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

ċ
udf_split3.sql
(1k)
Andy Hughes,
Jul 16, 2012, 12:08 PM
Comments