I need this for work real quick, and hopefully I’ll get some time to update the content later on.

This User Defined Function will turn a comma separated list (or any single character, for that matter), and turn it into a table so you can join on it. My recommendation is to shove it into a table variable first, rather than join on the function. It may be an old wives tale, but I’d rather do it this way once, than go through all of the procedures that use it to extract it out 🙂

CREATE FUNCTION dbo.udf_ListToTable
(
	@Input VARCHAR(8000),
	@Delimiter VARCHAR(1)
)
RETURNS @Return TABLE (ID INT IDENTITY, [Value] VARCHAR(1000))
AS
BEGIN
	-- Fill the table variable with the rows for your result set

    DECLARE @String VARCHAR(1000)

    WHILE LEN(@Input) > 0
    BEGIN
        SET @String = LEFT(@Input,
			ISNULL(NULLIF(CHARINDEX(@Delimiter, @Input) - 1, -1),
			LEN(@Input)))
        SET @Input = SUBSTRING(@Input,
			ISNULL(NULLIF(CHARINDEX(@Delimiter, @Input), 0),
			LEN(@Input)) + 1, LEN(@Input))

        INSERT INTO @Return ([Value])
			VALUES (@String)
    END

    RETURN
END

To use, do the following:

DECLARE @Fruit TABLE (ID INT, Fruit VARCHAR(50))
INSERT INTO @Fruit
	SELECT	ID, [Value]
	FROM	dbo.udf_ListToTable('Apple,Banana,Kiwi,Strawberry', ',')

RJ Hatch

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.