how to parse out comma separated values using SQL

Hi,

I have a table:
| email address | codes |
| justin@email.com | a100, b200, c400 |
| jim@email.com | b200 |

How can I create a new table

| email address | codes |
| justin@email.com | a100 |
| justin@email.com | b200 |
| justin@email.com | c400 |
| jim@email.com | b200 |

thanks
jecommeraAsked:
Who is Participating?
 
jecommeraConnect With a Mentor Author Commented:
figured it out so closing case
0
 
johanntagleCommented:
This question has come up many times with just slight variations.  Do a search here in EE and you'll find them.  See http://www.experts-exchange.com/Database/MySQL/Q_24840191.html for example
0
 
Éric MoreauSenior .Net ConsultantCommented:
is it for MS SQL or MySQL? You have both zones selected !
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
jecommeraAuthor Commented:
Hi,

The application uses an Oracle database.
I need to enter SQL queries to build tables.
0
 
xtermCommented:
I would dump the existing table, and then just use a shell script to go through it and write the queries for the new table.

For example, if you dump the table in exactly the format you show above to the file dump.txt, you can use the shell script below to generate the SQL commands which you can then paste into Oracle to populate the new table.  This does not create the new table, so you will have to do that yourself prior to running the generated SQL queries.
#!/bin/sh

OLDTABLE="dump.txt"
TARGET="newtable.sql"

for ROW in `cat $OLDTABLE | tr -d " "`;
do
        EMAIL_ADDR=`echo $ROW | cut -d"|" -f2`
        CODES=`echo $ROW | cut -d"|" -f3`
        if [ `echo $CODES | grep -c ","` -gt 0 ]; then
                for CODE in `echo $CODES | tr "," "\n"`;
                do
                        echo "INSERT INTO newtable (email_address,codes) VALUES ('$EMAIL_ADDR','$CODE'" >> $TARGET
                done
        else
                echo "INSERT INTO newtable (email_address,codes) VALUES ('$EMAIL_ADDR','$CODES'" >> $TARGET
        fi
done

Open in new window

0
 
jecommeraAuthor Commented:
Hi,

thanks for the help.

Unfortunately, it is a web interface so we will not be able to use any scripting. We can only use SQL queries to create new tables out of already existing tables.
0
 
xtermCommented:
I don't think SQL is sophisticated enough by itself to pull that off, but let me think about it....
0
 
xtermCommented:
I don't think SQL is sophisticated enough by itself to pull that off, but let me think about it....
0
 
jecommeraAuthor Commented:
sure - looking forward to your response ...
0
 
Éric MoreauSenior .Net ConsultantCommented:
why are you posting is MySQL and SQL Server 2008 if you are only interested in Oracle?
0
 
jecommeraAuthor Commented:
figured it out so closing case
0
 
sdstuberCommented:
create the type and function below and then run this query...


SELECT o.email_address, trim(x.COLUMN_VALUE) code
  FROM oldtable o, TABLE(str2tbl(o.codes)) x




CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);

            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;

        RETURN;
		WHEN NO_DATA_NEEDED THEN
		null; 
    END str2tbl;

Open in new window

0
 
sdstuberCommented:
if you figured it out, please post your solution, otherwise just delete the question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.