CREATE TABLE [dbo].[NumberList] (
    [Number]     INT        IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_NumberList] PRIMARY KEY CLUSTERED ([Number] ASC)
);

SELECT     SUBSTRING([Values].Value, NumberList.Number, CHARINDEX(',', [Values].Value + ',', NumberList.Number) - NumberList.Number) AS SplitValue
FROM         [Values] INNER JOIN
                      NumberList ON LEN([Values].Value) >= NumberList.Number
WHERE     (SUBSTRING(',' + [Values].Value + ',', NumberList.Number, 1) = ',')

quote:

---

Originally posted by notmyrealname

I have a numbers table in my database that I reference frequently. Mostly to provide row numbers. I like it more than the ROW_NUMBER function.

CREATE TABLE [dbo].[NumberList] (
    [Number]     INT        IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_NumberList] PRIMARY KEY CLUSTERED ([Number] ASC)
);

Anyway, if you create a NumberList table with just an integer column called Number and populate with say 1-10000 you can use the following query.

SELECT     SUBSTRING([Values].Value, NumberList.Number, CHARINDEX(',', [Values].Value + ',', NumberList.Number) - NumberList.Number) AS SplitValue
FROM         [Values] INNER JOIN
                      NumberList ON LEN([Values].Value) >= NumberList.Number
WHERE     (SUBSTRING(',' + [Values].Value + ',', NumberList.Number, 1) = ',')

... where Values is a table that has a column called Value with all of your comma seperated values.

It's really fast. I don't know how it stacks up the tally table solution. I am still running SQL Server 2005. You might be able do this in 2008+ without the numbers table. Maybe even 2005.

---