Split a column into multiple rows

Question

I have a table with a column that contains several values ​​separated by a comma (,), and would like to split it so that I get the Earch site in my row, but with the same number in front.

So, my choice will be from this entrance

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 what I thought would work, but no.

 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 actually had a piece of work that selects all the time (I was a crappy tester :() described above, but the only problem is that it loses the last value of the site, but I try to work a little on this occasion.

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

And the correct answer.

 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 2

You tried to answer Michael Sawyer on What is the best way to break csv lines 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; 

Answer 3

------------ Create a table of results ----------------------------- ------ --------

create table resulTable (

cnumber number

Website varchar2 (1000)

);

------------ Creating a splitter procedure ----------------------------- ------ ---

/ Here I replaced the numbers, for example: 2-78 with s2ss78s to use DBMS_UTILITY.comma_to_table (it does not work with numbers) /

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

ptablen BINARY_INTEGER;

ptab DBMS_UTILITY.uncl_array;

to begin

DBMS_UTILITY.comma_to_table (

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

tablen => ptablen,

tab => ptab);

FOR i IN 1 .. ptablen LOOP

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

END LOOP;

END;

------------ PL / SQL block to execute the procedure for each row -------------------

to begin

for I'm in (select cnumber, Site from Sitetable)

hinges

split_list_to_rows (i.cnumber, i.Site);

end of cycle;

end;

------------------------ See the result ----------------- ------ ----------------

select * from resulTable;

Answer 4

I think you have a maximum limit of 100 values, separated by a comma, which is not necessary, although it is probably not harmful to your case.

instead

 from Sitetable,(select level seq from dual connect by level <= 100) 

this should work for any number of values ​​(the level is limited by the maximum number of commas available).

 from Sitetable,(select level seq from dual connect by level <= (select max((LENGTH(site)-LENGTH(REPLACE(site,',', '' ))) + 1) from sitetable)