Solved

Comma Delimited Value String returned in Separate Records

Posted on 2009-07-09
5
1,252 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 143

Expert Comment

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

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 35

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 74

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.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 recover a database from a user managed backup

839 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