During the course of two days in the MSDN TSQL forums I must have seen 10 requests that were basically the same issue. Some string of values is passed from your application and you need to split them into the individual items. Here is a quick implementation that will get you a string splitting inline function that requires no recursion.
First, we need to create a numbers table that we will later utilize for string parsing. I’m using a version Itzik Ben-Gan’s number table that he posts frequently in SQL Mag articles.
-- Create and populate an auxiliary table of numbers (1,000,000 entries)
IF OBJECT_ID('dbo.Numbers') IS NOT NULL
DROP TABLE dbo.Numbers;
GO
CREATE TABLE dbo.Numbers
(
number INT NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED
);
SET NOCOUNT ON;
DECLARE
@max AS INT,
@rc AS INT;
SET @max = 1000000;
SET @rc = 1;
BEGIN TRAN;
INSERT INTO dbo.Numbers(number) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Numbers(number)
SELECT number + @rc
FROM dbo.Numbers;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Numbers(number)
SELECT number + @rc
FROM dbo.Numbers
WHERE number + @rc <= @max;
COMMIT TRAN;
GO
Next, we create the inline function that will do the heavy lifting for us. I’ve added an input for separator as hard coding the comma almost always guarantees some other character will later be needed.
IF OBJECT_ID('dbo.SplitString', 'IF') IS NOT NULL
DROP FUNCTION dbo.SplitString;
GO
CREATE FUNCTION dbo.SplitString(@arr AS VARCHAR(8000), @sep AS CHAR(1))
RETURNS TABLE
AS
RETURN
SELECT
(number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1 AS pos,
SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number) AS element
FROM dbo.Numbers
WHERE number <= LEN(@arr) + 1
AND SUBSTRING(@sep + @arr, number, 1) = @sep;
GO
Finally, we call our function with a string of values. If we needed to then utilize this for filtering we could use the CROSS APPLY operator introduced in SQL 2005.
DECLARE @arr varchar(8000);
DECLARE @sep char(1);
SET @arr = '1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C';
SET @sep = ',';
SELECT *
FROM dbo.SplitString(@arr, @sep);
This will give us a two row result set containing a position and value. Simple, easy, and set based to boot! And now I can just reference this post instead of cutting and pasting code. :)
68416ffc-31cb-4646-b8b6-0ccef764dd76|1|5.0