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