Solved

Comma Delimited Value String returned in Separate Records

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

Expert Comment

by:sdstuber
ID: 24827134
glad I could help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
statspack purge automate 7 53
null value 15 94
Schema creation in Oracle12c 6 39
Oracle query output question 4 36
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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.
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.

863 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

23 Experts available now in Live!

Get 1:1 Help Now