Cool Tips‎ > ‎String Manipulation‎ > ‎

String Manipulation - Alternate String Splitter

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:        22 Feb 2011

Description

This is a version of the string splitting function that utilizes a tally (or numbers) table to traverse and split a string in one pass without using a loop or CTE.
It references the function udf_NumbersTable to create the tally table - this comes in several variants with applicability from SQL 2000 onward and can be found in the data conversion section of this site.

I converted the original code snippet into a table valued function and parameterized the delimiter.  It will accept any single character delimiter (although a comma will probably be used for 99% of cases).  The limitation here is that the delimiter can only be a single character (again, this will be the case in a large umber of uses).

Code

Function:


DROP FUNCTION dbo.udf_split2
GO

CREATE FUNCTION dbo.udf_split2
(
  
@Parameter VARCHAR(8000),
  
@Delimiter CHAR(1)
)
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 a tally table (generated using the function
           udf_NumbersTable)

Author:        Unknown
History        22 Feb 2011
           16 Jul 2012 - ChillyDBA - Converted to function and altered to
           use the function udf_NumbersTable()

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

BEGIN

-- Add start and end delimiters to the Parameter so we can handle    
-- single elements  
  
SET @Parameter = @Delimiter + @Parameter + @Delimiter

  
-- Join the Numbers table to the string at the character level and    
   -- when we find a delimiter, insert what's between that delimiter and      
   -- the next delimiter into the Elements table
  
INSERT INTO @Results (Value)
  
SELECT SUBSTRING(@Parameter, i+1,CHARINDEX(@Delimiter,@Parameter,i+1)-i-1)  
  
FROM dbo.udf_NumbersTable(1, DATALENGTH(@Parameter), 1)
  
WHERE i < LEN(@Parameter)    
   AND
SUBSTRING(@Parameter,i,1) = @Delimiter --Notice how we find the delimiter

  
RETURN
END


Test Code:


SELECT * FROM dbo.udf_split2 ('Element01,Element02,Element03,Element04,Element05', ',')

SELECT * FROM dbo.udf_split2 ('Element01^Element02^Element03^Element04', '^')


ċ
udf_split2.sql
(2k)
Andy Hughes,
Jul 16, 2012, 11:40 AM
Comments