Split column to multiple rows

Question

Hi!

I have table with a column that contains multiple values separated by comma (,) and would like to split it so I get earch Site on its own row but with the same Number in front.

So my select would from this input

table Sitetable

Number             Site
952240             2-78,2-89                                                                                                                                                                      
952423             2-78,2-83,8-34

Create this output

Number             Site
952240             2-78
952240             2-89
952423             2-78 
952423             2-83
952423             8-34

I found something that I thought would work but nope..

select Number, substr(
    Site, 
    instr(','||Site,',',1,seq),
    instr(','||Site||',',',',1,seq+1) - instr(','||Site,',',1,seq)-1)  Site
from Sitetable,(select level seq from dual connect by level <= 100) seqgen
where instr(','||Site,',',1,seq+1) > 0

Edit2: I see that I have actually had a part working select all the time (I was a crappy tester :(), the above one works but the only problem is that it looses the last Site value but Ill try to work a bit on that..

Edit3: Now its working

select Number, substr(
Site, 
instr(','||Site,',',1,seq),
instr(','||Site||',',',',1,seq+1) - instr(','||Site,',',1,seq)-1)  Site
from Sitetable,(select level seq from dual connect by level <= 100) seqgen
where instr(','||Site,',',1,seq) > 0

Answer 1

Did you try this

http://stackoverflow.com/questions/1089508/how-to-best-split-csv-strings-in-oracle-9i

Answer 2

And the correct answer is.

select Number, substr(
Site, 
instr(','||Site,',',1,seq),
instr(','||Site||',',',',1,seq+1) - instr(','||Site,',',1,seq)-1)  Site
from Sitetable,(select level seq from dual connect by level <= 100) seqgen
where instr(','||Site,',',1,seq) > 0

Answer 3

------------Create Result Table-------------------------------------------

create table resulTable(

cnumber number,

Site varchar2(1000)

);

------------Create Splitter Procedure--------------------------------------

/*Here I replaced numbers for example: 2-78 by s2ss78s for using
DBMS_UTILITY.comma_to_table(it doesn't work on numbers)*/

create or replace procedure split_list_to_rows(num number,plist varchar2) as

ptablen BINARY_INTEGER;

ptab DBMS_UTILITY.uncl_array;

begin

DBMS_UTILITY.comma_to_table (

list => replace(replace(CONCAT('s', plist),',',',s'),'-','ss'),

tablen => ptablen,

tab => ptab);

FOR i IN 1 .. ptablen LOOP

insert INTO resulTable VALUES (num,replace(ltrim(ptab(i),'s'),'ss','-'));

END LOOP;

END;

------------PL/SQL Block To Execute Procedure For Each Row-------------------

begin

for i in (select cnumber,Site from Sitetable)

loop

split_list_to_rows(i.cnumber,i.Site);

end loop;

end;

------------------------See The Result---------------------------------------

select * from resulTable;