[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to parse out comma separated values using SQL

Posted on 2011-10-23
14
Medium Priority
?
306 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:jecommera
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 37013803
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37014010
is it for MS SQL or MySQL? You have both zones selected !
0
 

Author Comment

by:jecommera
ID: 37014231
Hi,

The application uses an Oracle database.
I need to enter SQL queries to build tables.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:xterm
ID: 37014380
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
 

Author Comment

by:jecommera
ID: 37014389
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
 
LVL 19

Expert Comment

by:xterm
ID: 37014486
I don't think SQL is sophisticated enough by itself to pull that off, but let me think about it....
0
 
LVL 19

Expert Comment

by:xterm
ID: 37014487
I don't think SQL is sophisticated enough by itself to pull that off, but let me think about it....
0
 

Author Comment

by:jecommera
ID: 37014488
sure - looking forward to your response ...
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37014664
why are you posting is MySQL and SQL Server 2008 if you are only interested in Oracle?
0
 

Accepted Solution

by:
jecommera earned 0 total points
ID: 37014734
figured it out so closing case
0
 

Author Closing Comment

by:jecommera
ID: 37043486
figured it out so closing case
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37014738
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37014740
if you figured it out, please post your solution, otherwise just delete the question
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question