Split comma separated values in Oracle 9i

Question

In Oracle, I have columns called orderids

orderids
111,222,333
444,55,66
77,77

How can get the output as

Orderid
111
222
333
444
55
66
77
77

Answer 1

Try this:

 WITH TT AS
 (SELECT orderid COL1 FROM orders)
 SELECT substr(str,
                   instr(str, ',', 1, LEVEL) + 1,
                   instr(str, ',', 1, LEVEL + 1) -
                   instr(str, ',', 1, LEVEL) - 1) COL1
     FROM   (SELECT rownum AS r,
                    ','|| COL1||',' AS STR
               FROM   TT )
     CONNECT BY PRIOR r = r
         AND    instr(str, ',', 1, LEVEL + 1) > 0
         AND    PRIOR dbms_random.STRING('p', 10) IS NOT NULL
;

See this SQLFiddle