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)
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)
want to convert columns values in one field with all values as concatinated
ReplyDeleteselect name from emp will giv 10 names
i want all names in one field
output :- ABBY.RICE,ABDUL.KHALIQ,ABDULLAH.ZAREH,ABEBECH.GETACHEW,ABEEHA.SANAI,ABENI.THOMAS
SELECT SUBSTR (SYS_CONNECT_BY_PATH (user_name , ','), 2) csv
FROM (SELECT user_name , ROW_NUMBER () OVER (ORDER BY user_name ) rn,
COUNT (*) OVER () cnt
FROM fnd_user where rownum <=6)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;