Here's a FAST function that avoids using a loop and uses a clustered index seek on the output table by adding a primary key and using that in the WHERE clause.

I did not develop the idea to use the XML split myself...I found it some time ago from a Google search and can't give a proper reference to the developer. If anyone knows who came up with it and can give proper credit, please do.

CREATE FUNCTION dbo.tvfParseDelimitedString

(

@s-2 NVARCHAR(MAX) -- Delimited input string

,@Split CHAR(1) -- Delimiter used for the input string

)

RETURNS @Table TABLE

(

[ID] INT NOT NULL IDENTITY(1,1)

,[Value] NVARCHAR(MAX) NULL

,PRIMARY KEY ([ID])

,UNIQUE ([ID])

)

BEGIN

DECLARE @X XML

SET @X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')

INSERT INTO @Table

SELECT T.c.value('.','NVARCHAR(MAX)') AS [Value]

FROM @X.nodes('/root/s') T (c)

RETURN

/*

SELECT [Value]

FROM dbo.tvfParseDelimitedString(N'1,AAA,4,BB,777,XYZ',',')

WHERE [ID] > 0

*/

END

GO