SEARCH:
NAVIGATION:
This site will work and look better in a browser that supports web standards, but it is accessible to any browser or Internet device.
| N U T S H E L L | |
| Module | list2tableIndexed |
|---|---|
| Description | Converts a list to a table with array indexing |
| Environment | SQL Server 2000/2005 |
| Author | Michael Sorens |
| Since | CleanCode 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) )
INSERT INTO @myList
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.
list2tableIndexed(@list, @delimiter)
where:
@list ::= "'" { [item] [delimiter] } [item] "'"
@delimiter ::= [char]
select * from list2tableIndexed('item1,item2,item3', ',')
select * from list2tableIndexed('item1#item2##item4#', '#')
Adapted from Use Table-Valued Functions as Arrays in SQL Server.
See also: list2table
