How to create multi-row output for a Comma seperated value

Say for example we have a string 'A,B,C,D,E,F'. We would like to have it printed in separate lines say

A
B
C
D
E
F

Like so.

Now we can use a simple SQL statement to convert the same.

SELECT SUBSTR('A,B,C,D,E,F',
INSTR('A,B,C,D,E,F',',',1,
LEVEL)-1,1) FROM DUAL
CONNECT BY LEVEL <= LENGTH(
REPLACE('A,B,C,D,E,F',','))


To elaborate the technique, we have used the following logic:

1. Used CONNECT BY statement to find out how many rows are necessary
2. INSTR to find out the place of each row
3. SUBSTR to cut the string between the comma

Well what was the assumptions before using this query. The string between comma are having length of one. Any number of strings can be given in this fashion.

7 comments :

  1. Sorry, but your statement sql doesn't work well; return only A.
    By

    ReplyDelete
  2. Which version of Oracle are you using?

    ReplyDelete
  3. Hi,
    Oracle 9.2.0.4.0
    Best regards.

    ReplyDelete
  4. Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.6.0 - Production

    SQL> SELECT SUBSTR('A,B,C,D,E,F',
    2 INSTR('A,B,C,D,E,F',',',1,
    3 LEVEL)-1,1) FROM DUAL
    4 CONNECT BY LEVEL <= LENGTH(
    5 REPLACE('A,B,C,D,E,F',','));

    S
    -
    A

    SQL> ed
    Wrote file afiedt.buf

    1 select count(*) from
    2 (SELECT SUBSTR('A,B,C,D,E,F',
    3 INSTR('A,B,C,D,E,F',',',1,
    4 LEVEL)-1,1) FROM DUAL
    5 CONNECT BY LEVEL <= LENGTH(
    6* REPLACE('A,B,C,D,E,F',',')))
    SQL> /

    COUNT(*)
    ----------
    6

    -----------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> SELECT SUBSTR('A,B,C,D,E,F',
    2 INSTR('A,B,C,D,E,F',',',1,
    3 LEVEL)-1,1) FROM DUAL
    4 CONNECT BY LEVEL <= LENGTH(
    5 REPLACE('A,B,C,D,E,F',','));

    S
    -
    A
    B
    C
    D
    E
    F

    6 rows selected.

    ----------------------------------

    There is something wrong with output formating in 9i.

    :-)))

    ReplyDelete
  5. It is not something wrong, oracle has only enabled the recursive call functionality with dual table from 10g

    ReplyDelete
  6. Hi,
    in effect your post is correct.
    I resolve the question in a simple mode:
    select * from (
    SELECT SUBSTR('A,B,C,D,E,F',
    INSTR('A,B,C,D,E,F',',',1,
    LEVEL)-1,1) FROM DUAL
    CONNECT BY LEVEL <= LENGTH(
    REPLACE('A,B,C,D,E,F',','))
    );

    Thanks a lot for your support.

    ReplyDelete
  7. This query will only stripe out single character output. For any length string output refer my addendum post at http://askanantha.blogspot.com/2007/04/how-to-create-multi-row-output-for_11.html

    ReplyDelete