Great article Mike, thanks, and what a neat little function SetSplit is! I have added it to my toolkit (and given you and SQLServerCentral the credit), but added a second parameter for the character to split the string with.

I had to sit down and figure out how it worked however...

I was wondering how you could number the result set sequentially, so that with you could select from the function to return the nth word in the result set. I don't think you can use an of the methods I would normally use,  IDENTITY(), or count(*) and a GROUP BY

David

Thanks David.  If you're on SQL 2005 it's a simple matter of converting the function to a CTE and adding a ROW_NUMBER() to it.  I set it up that way initially but noticed some performance was lost.  For SQL 2000 it's more complicated, and your best bet might be to change the UDF to a regular table-valued function and INSERT the results into a table variable with an IDENTITY column.  Again,  you'll lose performance that way, but depending on your situation it might be worth it.

Below is an example on SQL 2005 using a CTE and the ROW_NUMBER() function.  Note that I don't have a SQL 2005 installation handy, so I wasn't able to test this before posting.  There may be a syntax error or two in it, but you get the idea.

CREATE FUNCTION dbo.fnSetSplit (@String VARCHAR(8000))
RETURNS TABLE
AS
RETURN (
    WITH Splitter(Num, String) AS
    (
    SELECT Num,
        SUBSTRING(@String,
            CASE Num
                WHEN 1 THEN 1
                ELSE Num + 1
            END,
            CASE CHARINDEX(',', @String, Num + 1)
                WHEN 0 THEN LEN(@String) - Num + 1
                ELSE CHARINDEX(',', @String, Num + 1) - Num -
                    CASE
                        WHEN Num > 1 THEN 1
                        ELSE 0
                    END
               END
            ) AS String
    FROM dbo.Numbers
    WHERE Num <= LEN(@String)
        AND (SUBSTRING(@String, Num, 1) = ','
            OR Num = 1)
    )
    SELECT ROW_NUMBER() OVER (ORDER BY Num) AS Num, String
    FROM Splitter
)

In addition to the benefits of a NUMBERS table shown by Micahel, the ability to intentionally generate a Cartisian product is anoter use of the table.  I'm referring to a Cartesion product where you want X number of rows not to perform some function but to get X number of rows of data that would otherwise require X number of UNION statements.  In our company we provide custom reporting for clients.  On a few occassions I have seen some of our 'SQL Knowledge Limited' report writers create code that contains several queries UNION'd  together because they need to generate a specific number of records from a query that would normally generate just 1 record.  I don't recall the specifics, only that I improved the query's performance greatly by using a NUMBERS table approach to generate the X number of copies of a record that the prior UNION version was doing.

On a similiar note a DATE table is another wonderful utility table that works similiar to a NUMBERS table.  I have a utility table I call DATELOOKUP that conatins 1 row for every date (MM/DD/YYYY) between a range of years that covers any time frame that a client would need to report on for now and for the next 10 years.  Each row has many columns, each column containing a piece of info specific to the date.  For example in our business it's important to use the first day of a month specified by the user.  Without the date table, a combination of Date functions (like DateAdd) have to be used to get the first day of whatever MM/DD/YYYY a user enters as criteria.  WIth the DATELOOK table I can join the data in DATELOOKUP to the date the user specifies and return the column from that record that contains the first day of said Month/Year.  IN fact the code for generating and using the DATELOOKUP table is on the SQLServerCentral site within sample scripts.

Excellent article and information!

Thanks for sharing!

Ed

You might want to have a look at

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

Which shows how this sort of thing can be done in v2005.

Must admit I never create a permanent table like this but always create it on the fly - either as a derived table or table variable.

Celko definitely knows what he's doing and he writes good books.  I think people just get rubbed the wrong way by some of his responses on the newsgroups... but that's neither here nor there...

Aaron Bertrand has some excellent articles on using an Auxiliary Calendar Table over at ASPFAQ:  http://www.aspfaq.com/show.asp?id=2519.  I highly recommend using a date-based calendar table similar to the one he describes, because of the flexibility.  You can use a calendar table like his to mark off regional holidays, easily calculate working days in a time period, etc.  While you could probably do the same type of thing with a numbers table, it won't be as easy or as intuitive.

For Nigel: I prefer to use a single permanent numbers table.  You eliminate the cost of re-creating it over and over (can become pretty substantial if you're re-creating it a lot and inserting a lot of numbers into it) and you can get a potential performance boost by using WITH SCHEMABINDING with UDF's that reference your permanent numbers table.

Thanks

What a useful trick, and definitely something I'll need to remember.

However, I think the function has a bug.  When I put through either of the following...

SELECT * FROM dbo.fnSetSplit(',C,,,E')

SELECT * FROM dbo.fnSetSplit(',,C,,,E')

...I get odd results before 'C'.  Instead, I knocked up the below with some adjustments:

i) the Numbers table starts from 0, not 1
ii) accepts a variable delimiter, including one of variable length;
iii) returns a row number so that you can choose the nth value.
iv) use "like" for comparisons as SQL Server can do funny things comparing spaces

It does have a "quirk", however.  If the delimiter is a multiple of the same character you can get odd results.  Whether this is an error or not I think depends on the expected usage.

SELECT * FROM dbo.fnSetSplitSV(',,C,,,E',',') -- picks up the "missing" A

SELECT * FROM dbo.fnSetSplitSV(' TA fred TA mary TA albert',' TA ') -- variable delimiter

SELECT * FROM dbo.fnSetSplitSV(',,,C,,,E',',,') -- multiple delimiter with "quirk"

S.

CREATE FUNCTION [dbo].[fnSetSplitSV] (@String VARCHAR(8000), @Delim VARCHAR(5))