Split column to multiple rows
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,开发者_JAVA百科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
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
Did you Try Michael Sofaer's answer to How to best split csv strings in oracle 9i
create or replace function splitter_count(str in varchar2, delim in char) return int as
val int;
begin
val := length(replace(str, delim, delim || ' '));
return val - length(str);
end;
create type token_list is varray(100) of varchar2(200);
CREATE or replace function tokenize (str varchar2, delim char) return token_list as
ret token_list;
target int;
i int;
this_delim int;
last_delim int;
BEGIN
ret := token_list();
i := 1;
last_delim := 0;
target := splitter_count(str, delim);
while i <= target
loop
ret.extend();
this_delim := instr(str, delim, 1, i);
ret(i):= substr(str, last_delim + 1, this_delim - last_delim -1);
i := i + 1;
last_delim := this_delim;
end loop;
ret.extend();
ret(i):= substr(str, last_delim + 1);
return ret;
end;
------------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;
I think you have a max limit of 100 comma separated values which is unnecessary, though probably not harmful in your case.
instead of
from Sitetable,(select level seq from dual connect by level <= 100)
this should work for any number of values (level limited by the max number of commas present).
from Sitetable,(select level seq from dual connect by level <= (select max((LENGTH(site)-LENGTH(REPLACE(site,',', '' ))) + 1) from sitetable)
Use cross join:
SELECT Number, SiteNumber
FROM Sitetable
CROSS APPLY STRING_SPLIT(Site, ',');
精彩评论