[dba-SQLServer] A UDF to turn a list into a table

I just dug up some code I wrote a long while back, because suddenly I needed it. It's a function that parses a comma-delimited string and places the contents into a table, then returns the table. 

Lots of front ends assemble a string and then pass it to a stored procedure or function that then uses the string as the argument to an IN() clause. This is woefully inefficient, and there is a better way. The following function does what is described above. The cool thing is that you can join the result table to some other table, with a significant performance gain over the typical IN() approach.

Here is the function, followed by an example call:

<code>
CREATE FUNCTION dbo.StringToTable_fnt 
(
@String varchar(100)
)
RETURNS @Values TABLE (ID int primary key)
AS
BEGIN
DECLARE @pos int
DECLARE @value int
WHILE @string > ''
BEGIN
SET @pos = CHARINDEX(',', @string)
IF @pos > 0 
BEGIN
SET @value = SUBSTRING( @string, 1, @pos - 1)
SELECT @string = LTRIM(SUBSTRING( @string, @pos + 1, LEN(@string)- at pos+1))
INSERT @Values SELECT @value
END
ELSE
IF LEN(@string) > 0
BEGIN
SET @value = @string
INSERT @Values SELECT @value
SET @string = ''
END
ELSE
SET @string = ''
END
RETURN
END
-- SELECT * FROM dbo.StringToTable_fnt('100, 200, 300, 400, 500')
</code>

Don't mind the formatting. I pasted it directly from SSMS. You can clean it up.

Hope this helps someone, 
Arthur Fuller
Technical Writer, Data Modeler, SQL Sensei
Artful Databases Organization
www.artfulsoftware.com