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.
Posted by Labels: , ,

7 comments :

  1. AnonymousApril 16, 2007 at 7:15 PM

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

    ReplyDelete
  2. Anantha NarayananApril 16, 2007 at 7:41 PM

    Which version of Oracle are you using?

    ReplyDelete
  3. AnonymousApril 16, 2007 at 8:08 PM

    Hi,
    Oracle 9.2.0.4.0
    Best regards.

    ReplyDelete
  4. stradivariApril 17, 2007 at 4:43 AM

    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. Anantha NarayananApril 17, 2007 at 11:39 AM

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

    ReplyDelete
  6. AnonymousApril 17, 2007 at 9:33 PM

    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. Anantha NarayananApril 18, 2007 at 10:53 AM

    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

Subscribe to: Post Comments ( Atom )