select count(*) from emp

create table emp 
(department_id number primary key, 
 employee_email varchar2(4000))

insert into emp values(10, 'richard.jones@gmail.com:elizabeth.phelps@gmail.com:christopher.taylor-johnson@outlook.com :steven.king@yahoo.com')

insert into emp values(20, 'sylvia.plath@outlook.com:steven.king@yahoo.com:jack.g.london@mail.com:richard.jones@gmail.com :t.s.white@gmail.com:emily.temple-wood@yahoo.com:john.ruskin@rediff.com:jane.austen@gmail.com')

insert into emp values(40, 'elizabeth.phelps@gmail.com:albert.camus@gmail.com:steven.king@yahoo.com:george.bernard.shaw@gmail.com: haruki.murakami@gmail.com:j.r.r.tolkien@rediffmail.com:frederick.douglass@gmail.com: ralph.waldo.emerson@mail.com:jane.austen@gmail.com:percy.bysshe.shelley@yahoo.com:richard.jones@gmail.com')

insert into emp values(50, 'steven.king@yahoo.com')

insert into emp values(60, 'steve.jones-smith@inbox.com:edgar.allan.poe@outlook.com:henry.david.thoreau@gmail.com:audrey.jameson-wright@gmail.com:jane.austen@gmail.com:sylvia.plath@outlook.com')

select count(*) from emp

with t as 
    ( select department_id, employee_email str 
      from emp 
    ), 
    elements as ( 
      select department_id, regexp_substr(replace(str,':',','),'[^,]+', 1, lev) AS elem 
      from   t, lateral ( 
        select level lev from dual 
        connect by regexp_substr(replace(str,':',','), '[^,]+', 1, level) is not null 
     ) 
   ) 
   select department_id, listagg(distinct elem,',') within group (order by elem) as tags 
   from elements 
   group by department_id