Solved

Comma Delimited Value String returned in Separate Records

Posted on 2009-07-09
5
1,248 Views
Last Modified: 2012-06-22
To simplify this, I am running a query that returns one record with two columns. The first column has the code and the second has a single string with multiple values delimited by commas.  I would like the query to return one record for every comma delimited value in the second column.  

Here is what I currently get:

Code                Values
---------             -----------------
Code1              123,456,789


Here is what I would like to get:

Code                Values
---------             -----------------
Code1              123
Code1              456
Code1              789


I am sure I would need some kind of custom function or stored procedure. Any help would be greatly appreciated.
0
Comment
Question by:cortilius
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24818636
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 24819826
another way


SELECT   code, x.COLUMN_VALUE VALUE
  FROM   yourtable, table(str2tbl(vals)) x

code for str2tbl (or similar functions) can be found on asktom and lots of other sites

here's my version (they are all pretty much the same)
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;

    END str2tbl;

Open in new window

0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 24824718
If you find (or write) a query or function that returns the data the way you want, you could then construct a view that does this (complex work) for you.  This allows you to just use a very simple query from the view when you need the data.
0
 

Author Closing Comment

by:cortilius
ID: 31601848
That code wroked great.  I created the custom function and type and everything works great.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24827134
glad I could help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now