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