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 with array indexing
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 list2table, list2tableIndexed returns a two-column table. The ID column contains a generated IDENTITY value that serves as an array index. That is, the first item in your input list will have an ID of 1, the second 2, and so on. This provides a convenient mechanism for referencing array elements by index, e.g.:

DECLARE @myList TABLE ( id int, name VARCHAR(10) )
   SELECT * FROM [list2tableIndexed]('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

list2tableIndexed(@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[id as int, data as varchar(256)]
The two-column table returned contains an index in the first column, i.e. a row number, and a datum in the second column. The table will always return strings in the data field, 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 list2tableIndexed('item1,item2,item3', ',')
Octothorp-separated list of 5 items
	select * from list2tableIndexed('item1#item2##item4#', '#')

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

See also: list2table

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