SQL SERVER – UDF – Function to Convert List to Table

Following Users Defined Functions will convert list to table. It also supports user defined delimiter. Following UDF is written for SQL SERVER 2005. It will also work well with very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

CREATE FUNCTION dbo.udf_List2Table
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
@ParsedList TABLE
(
item VARCHAR(MAX)
)
AS
BEGIN
DECLARE @item VARCHAR(MAX), @Pos INT
SET @List = LTRIM(RTRIM(@List))+ @Delim
SET @Pos = CHARINDEX(@Delim, @List, 1)
WHILE @Pos > 0
BEGIN
SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
IF @item <> ''
BEGIN
INSERT INTO @ParsedList (item)
VALUES (CAST(@item AS VARCHAR(MAX)))
END
SET @List = RIGHT(@List, LEN(@List) - @Pos)
SET @Pos = CHARINDEX(@Delim, @List, 1)
END
RETURN
END
GO

Run following script to test above UDF.
----Example 1 for VARHCAR
SELECT item AS Example1
FROM dbo.udf_List2Table('first||2nd||III||1+1+1+1','||')
GO
----Example 2 for INT
SELECT CAST(item AS INT) AS Example2
FROM dbo.udf_List2Table('111,222,333,444,555',',')
GO

ResultSet:
Example1
————-
first
2nd
III
1+1+1+1

(4 row(s) affected)

Example2
————-
111
222
333
444
555