Solved

Comma Delimited Value String returned in Separate Records

Posted on 2009-07-09
5
1,254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

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