Using Connect By in SQL

Connect By In SQL.


example :- i have sting like 'aactranportes@uol.com.br; ; d.ojea@seafrigo.com; a.ferrao@seafrigo.com'.
i want to split these email properly into rows.


select *
from (
SELECT level,
trim(substr(email,
instr(';' || email || ';', ';', 1, rownum),
instr(';' || email || ';', ';', 1, rownum + 1) -
instr(';' || email || ';', ';', 1, rownum) - 1)) email
FROM (select 'aactranportes@uol.com.br; ; d.ojea@seafrigo.com; a.ferrao@seafrigo.com' email from dual)
CONNECT BY LEVEL <= 10) wrk
where nvl(length(email),0) > 0



Same convert into columnsss



select email,
(substr(email,1,instr(email,';',1)-1)) email1,
(substr(email,instr(email,';',1)+1,instr(email,';',1,2)-instr(email,';',1)-1)) email2,
(substr(email,instr(email,';',1,2)+1)) email3
from
(select 'aactranportes@uol.com.br;d.ojea@seafrigo.com;a.ferrao@seafrigo.com' email from dual)