Parsing CSV Values Into Multiple Rows

This article explains how to take a string of comma-separated values
(CSV), and parse out the values individually. If you have a table with
a column of CSV values, and you want to translate them into a normalized
table, it can really difficult. You would need to use a cursor to loop
through your CSV table and process each row. Fortunately there's a
way to do this using just a regular SELECT. It requires a table of numeric
values, but it does the job beautifully; you can INSERT normalized
CSV values into a table with one SQL statement. The tricky part is
that each word can be a different length, and each CSV can have a
different number of words in it. We'll need a separate table (Tally),
with a single column of sequential integer values. The "tally table" is
mentioned in Joe Celko's SQL for Smarties -- the best SQL book ever
written -- as a handy source for a numeric sequence. Here, it will
replace the loop needed to parse out each value from the CSV string.


SELECT Author,
NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word
FROM Tally, Quotes
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ',' AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0


Article from SQLTeam, more