Posts

Showing posts from October, 2005

Parsing CSV Values Into Multiple Rows

Image
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