This site will work and look better in a browser that supports web standards, but it is accessible to any browser or Internet device.

CleanCode logo
NAVIGATION: first page in sectionprevious pagenext pagefinal page in section


DescriptionConverts a list to a table
EnvironmentSQL Server 2000/2005
AuthorMichael Sorens
SinceCleanCode 0.9.17

This function converts a list, passed as a single string, into a table. The string enumerates all your list elements separated by a single character of your choice. In contrast with list2tableIndexed, list2table returns a one-column table. This function is appropriate if you do not need array indexing of your list in the returned table. However, you could still manipulate it that way after the fact, if needed, by using the IDENTITY attribute on your local table variable, e.g.:

DECLARE @myList TABLE ( id int IDENTITY(1,1), name VARCHAR(10) )
   SELECT * FROM [list2table]('item1/item2/item3', '/')
select name from @myList where id=2


Note that the presence of a separator character always indicates an element on both sides. Specifically, if you use a comma as a delimiter, then "A,,B,C" returns a table with 4 rows, not 3. The second row value is an empty string. Similarly, "A,B,C," and ",A,B,C" and "A,,B,C" also return tables with 4 rows, with empty strings inserted where no data is provided.

Usage and Parameters

list2table(@list, @delimiter)
	@list      ::= "'" { [item] [delimiter] } [item] "'"
	@delimiter ::= [char]
@list [ Type: VARCHAR(2000) Default: -none- ]
A string containing a list of items separated by the specified @delimiter. Whitespace within the @list is ignored.
@delimiter [ Type: CHAR(1) Default: ',' ]
A character used between each pair of items in the @list.
Returns: table[data as varchar(256)]
The one-column table returned contains each element of the input list. The table will always return strings, irrespective of the input. It is up to you to convert values to other types if you need to.


Comma-separated list of 3 items
	select * from list2table('item1,item2,item3', ',')
Octothorp-separated list of 5 items
	select * from list2table('item1#item2##item4#', '#')

Adapted from Use Table-Valued Functions as Arrays in SQL Server.

See also: list2tableIndexed

Get CleanCode at Fast, secure and Free Open Source software downloads
Copyright © 2001-2013 Michael Sorens • Contact usPrivacy Policy
Usage governed by Mozilla Public License 1.1 and CleanCode Courtesy License
CleanCode -- The Website for Clean DesignRevised 2013.06.30