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

Use this query to convert any length string in comma separated format to rows:

SELECT
SUBSTR('AA,BBB,C,D,E,F,G,H',
INSTR(CHR(44)||'AA,BBB,C,D,E,F,G,H'||
CHR(44),',',1,LEVEL),
INSTR('AA,BBB,C,D,E,F,G,H'||CHR(44),',',1,LEVEL)-
INSTR(CHR(44)||'AA,BBB,C,D,E,F,G,H'||
CHR(44),',',1,LEVEL))
FROM DUAL
CONNECT BY LEVEL <= LENGTH('AA,BBB,C,D,E,F,G,H')-
LENGTH(REPLACE('AA,BBB,C,D,E,F,G,H',','))+1
Posted by Labels: , ,

3 comments :

  1. AnonymousApril 16, 2007 at 2:18 AM

    select replace('AA,BBB,C,D,E,F,G,H',',',chr(10)) from dual

    ReplyDelete
  2. UnknownApril 19, 2007 at 8:10 PM

    we use a table functoin .... technique is widespread ... if you check askTom or other sites

    something along

    CREATE OR REPLACE TYPE stringTableType is table of varchar2(100);
    /

    CREATE OR REPLACE function List2Table( p_listString in varchar2, p_del in char )
    return stringTableType
    is
    l_str long default p_listString || p_del;
    l_n number;
    l_data stringTableType := stringTabletype();
    begin
    loop
    l_n := instr( l_str, p_del );
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) :=
    ltrim(rtrim(substr(l_str,1,l_n-1)));
    l_str := substr( l_str, l_n+1 );
    end loop;
    return l_data;
    end List2Table;
    /

    example
    select * from table(list2table('AA,BBB,C,D,E,F,G,H',','));

    I don't know which one is slower...

    ReplyDelete
  3. Anantha NarayananApril 23, 2007 at 4:56 PM

    Nice to have shared the information. Thanks

    ReplyDelete

Subscribe to: Post Comments ( Atom )