How to split string and save into an array in T-SQL

Question

I am writing a cursor to populate data in new table from main table which contains data in below manner

Item	Colors
Shirt	Red,Blue,Green,Yellow

I want to populate new Table data by fetching the Item and then adding it in row, according to each color it contains

Item	Color
Shirt	Red
Shirt	Blue
Shirt	Green
Shirt	Yellow

I am stuck in how to

1. Delimit/Split "Colors" string
2. To save it in an array
3. To use it in cursor

as I am going to use Nested cursor for this purpose.

Answer 1

Using Sql Server 2005+ and the XML datatype, you can have a look at the following

DECLARE @Table TABLE(
        Item VARCHAR(250),
        Colors VARCHAR(250)
)

INSERT INTO @Table SELECT 'Shirt','Red,Blue,Green,Yellow'
INSERT INTO @Table SELECT 'Pants','Black,White'


;WITH Vals AS (
        SELECT  Item,
                CAST('<d>' + REPLACE(Colors, ',', '</d><d>') + '</d>' AS XML) XmlColumn
        FROM    @Table
)
SELECT  Vals.Item,
        C.value('.','varchar(max)') ColumnValue
FROM    Vals
CROSS APPLY Vals.XmlColumn.nodes('/d') AS T(C)

Answer 2

The article Faking Arrays in Transact SQL details SEVERAL techniques to solve this problem, ranging from using the PARSENAME() function (limit to 5 items) to writing CLR functions.

The XML answer is one of the detailed techniques that can be chosen to a specific scenario.

Combining some of the tips, I solved my string split problem like this:

SET NOCOUNT ON;

DECLARE @p NVARCHAR(1000), @len INT;
SET @p = N'value 1,value 2,value 3,value 4,etc';
SET @p = ',' + @p + ',';
SET @len = LEN(@p);

-- Remove this table variable creation if you have a permanent enumeration table
DECLARE @nums TABLE (n int);
INSERT INTO @nums (n)
    SELECT A.n FROM 
    (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY TableKey) as n FROM dbo.Table) A
    WHERE A.n BETWEEN 1 AND @len;

SELECT SUBSTRING(@p , n + 1, CHARINDEX( ',', @p, n + 1 ) - n - 1 ) AS "value"
    FROM @nums
    WHERE SUBSTRING( @p, n, 1 ) = ',' AND n < @len;

Note that, considering 1000 your string length limit, you must have a table with 1000 or more rows (dbo.Table on the sample tsql) to create the table variable @nums of this sample. On the article, they have a permanent enumeration table.

Answer 3

For those who like to keep it simple:

    -- Here is the String Array you want to convert to a Table
declare @StringArray varchar(max)
set @StringArray = 'First item,Second item,Third item';

-- Here is the table which is going to contain the rows of each item in the String array
declare @@mytable table (EachItem varchar(50))

-- Just create a select statement appending UNION ALL to each one of the item in the array
set @StringArray = 'select ''' + replace(@StringArray, ',', ''' union all select ''') + ''''
-- Push the data into your table
insert into @@mytable exec (@StringArray)

-- You now have the data in an an array inside a table that you can join to other objects
select * from @@mytable