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

In the future, please spend more time formatting your question. Since you haven't formatted the expected and the actual output, all numbers were on the same line and the question rather misleading. I've fixed it this time. — Codo, Oct 05 '13 at 08:48
If you google for "oracle split string into rows", you'll find what your looking for, e.g. http://stackoverflow.com/questions/14328621/oracle-10g-splitting-string-into-multiple-rows. — Codo, Oct 05 '13 at 08:55
Nope @Codo, regular expressions were introduced in 10g, which means the usual ways of doing this won't work (which is the reason why I haven't closed). — Ben, Oct 05 '13 at 08:56
Good point. But it can be done with INSTR and SUBSTR as well. — Codo, Oct 05 '13 at 08:57
Yes, and [DBMS_UTILITY.COMMA_TO_TABLE](http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_util2.htm#1002109). — Ben, Oct 05 '13 at 08:58
Oracle 9i has been out of support for nearly a decade now. You should really think about upgrading to a supported version (which is 11.x or 12.x) — , Oct 05 '13 at 09:42

score 4 · Accepted Answer · edited Oct 06 '13 at 11:28

4

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
;

enter image description here

See this SQLFiddle

edited Oct 06 '13 at 11:28

Ben

51,770
36
127
149

answered Oct 05 '13 at 08:45

MrSimpleMind

7,890
3
40
45

i want each word in individual rows – Naveen Kumar Oct 05 '13 at 08:53

score 1 · Answer 2 · answered Oct 05 '13 at 09:35

This is one appraoch:

with order_table as (
  select '111,222,333' as orderids from dual
  union all select '444,55,66' from dual
  union all select '77,77' from dual
)
select substr(orderids, instr(orderids, ',', 1, lvl) + 1, instr(orderids, ',', 1, lvl + 1) - instr(orderids, ',', 1, lvl) - 1) orderid 
from
  ( select ',' || orderids || ',' as orderids from order_table ),
  ( select level as lvl from dual connect by level <= 100 )
where lvl <= length(orderids) - length(replace(orderids, ',')) - 1;

Just remove the WITH clause and replace the order_table with your real table.

score 0 · Answer 3 · answered Oct 05 '13 at 09:38

This too might help you,

with t(orderid) as 
(
SELECT '111,222,333' FROM dual
UNION
SELECT '444,55,66' FROM dual
UNION
SELECT '177,77' FROM dual
)
SELECT trim(x.COLUMN_VALUE.EXTRACT('e/text()')) cols 
FROM t t, TABLE (xmlsequence(XMLTYPE('<e><e>' || REPLACE(t.orderid,',','</e><e>')|| '</e></e>').EXTRACT('e/e'))) x;

score 0 · Answer 4 · edited Oct 05 '16 at 20:15

0

instr(','||NVL('972414AQ,972414AQ',I.CUSIP)||',', ','||I.CUSIP||',') > 0

This is the actual query I was looking for.

edited Oct 05 '16 at 20:15

RamenChef

5,557
11
31
43

answered Oct 05 '16 at 18:43

Naveen Kumar

81
2
10

Split comma separated values in Oracle 9i

4 Answers4