You are here:Home » tsql » SQL SERVER – UDF – Function to Convert List to Table

SQL SERVER – UDF – Function to Convert List to Table

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