Parse Function for Comma Separated List
By Kalman Toth, M.Phil., M.Phil., MCDBA

May 22, 2005

Frequently happens in database development or administration that the data is in list format. To convert it into table format you can use a table-valued User Defined Function (UDF). Following is an example for such a function.

CREATE FUNCTION fnParseList(@List varchar(4096))
RETURNS @Output TABLE
( Sequence int IDENTITY(1,1),
Item varchar(4096) )
AS
BEGIN
DECLARE @Pointer int
SET @Pointer = 0
WHILE (LEN(@List) > 0)
BEGIN
SET @Pointer = CHARINDEX(',', @List)
IF (@Pointer = 0) AND (LEN(@List) > 0)
  BEGIN
    INSERT @Output VALUES (@List)
    BREAK
  END
IF (@Pointer > 1)
  BEGIN
    INSERT @Output VALUES (LEFT(@List, @Pointer - 1))
    SET @List = RIGHT(@List, (LEN(@List) - @Pointer))
  END
ELSE
  SET @List = RIGHT(@List, (LEN(@List) - @Pointer))
END
RETURN
END
GO

To execute it:

SELECT * FROM dbo.fnParseList('X,Y,Z,A,B,C,D,E,F,U,V,W')
GO

This is the result set:

1 X
2 Y
3 Z
4 A
5 B
6 C
7 D
8 E
9 F
10 U
11 V
12 W