List to Table

Every now and again, a developer will be faced with the task of referincing a List of id's. It's typically irresistable to want to push all these IDs into a single field. If you're searching then you may end up considering a cursor... for that I decided to write the following function... it's like other Tsql functions that push a list to a table, but this is my take on it... I release the code here with out warranty.

CREATE FUNCTION udfListToTable (@HList VarChar(2000), @Delimiter CHAR(1))
RETURNS @ListTable TABLE (Field1 VARCHAR(6))
AS
BEGIN
--By: Francisco Tapia
--Date: 2/1/2005
--Purpose: To convert a Comma delimited text to a Temp Variable table To help avoid dynamic sql
-- Instead you can join the temp table or use it in your where clause if a field is IN the subquery
DECLARE @FieldText as VarChar(6)

IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
SET @HList = @HList + @Delimiter

WHILE CHARINDEX(@Delimiter, @HList) > 0
BEGIN
IF CHARINDEX(@Delimiter, @HList) > 0
BEGIN
SELECT @FieldText =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
END
ELSE
BEGIN
SELECT @FieldText = RTRIM(LTRIM(@HList))
END
--Insert into Variable Table
INSERT INTO @ListTable(Field1)
SELECT RTRIM(LTRIM(@FieldText))
--Remove Item from list
SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) - CHARINDEX(@Delimiter, @HList))
END
RETURN
END