Oracle SQL Practical question with answer extract names from mail id using SQL function

 

 

 

 

drop table tab1;

create table tab1(mail_id varchar2(50));

 

insert into tab1 values('dileep.a.kumar@gmail.com');

insert into tab1 values('subash.anand@yahoo.com');

insert into tab1 values('ram@outlook.com');

commit;

 

select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n

from tab1;

 

select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n,

 substr(mail_id,instr(mail_id,'@')+1) d

from tab1;

 

with d1 as (select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n,

 substr(mail_id,instr(mail_id,'@')+1) d

from tab1),

d2 as (select m,d,n,instr(n,'.',1,1) f_dot,

instr(n,'.',1,2) s_dot from d1)

select m,d,n,f_dot,s_dot,substr(n,1,decode(f_dot,0,length(n),f_dot)) fname

from d2

;

 

with d1 as (select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n,

 substr(mail_id,instr(mail_id,'@')+1) d

from tab1),

d2 as (select m,d,n,instr(n,'.',1,1) f_dot,

instr(n,'.',1,2) s_dot from d1)

select m,d,n,f_dot,s_dot,substr(n,1,decode(f_dot,0,length(n),f_dot)) fname

from d2

;

with d1 as (select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n,

 substr(mail_id,instr(mail_id,'@')+1) d

from tab1),

d2 as (select m,d,n,instr(n,'.',1,1) f_dot,

instr(n,'.',1,2) s_dot from d1)

select m,d,n,f_dot,s_dot,substr(n,1,decode(f_dot,0,length(n),f_dot)) fname,

decode(s_dot,0,null,substr(n,f_dot,s_dot-f_dot))

from d2

;

 

with d1 as (select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n,

 substr(mail_id,instr(mail_id,'@')+1) d

from tab1),

d2 as (select m,d,n,instr(n,'.',1,1) f_dot,

instr(n,'.',1,2) s_dot from d1)

select m,d,n,f_dot,s_dot,substr(n,1,decode(f_dot,0,length(n),f_dot)) fname,

decode(s_dot,0,null,substr(n,f_dot+1,s_dot-f_dot-1))

from d2

;

 

 

with d1 as (select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n,

 substr(mail_id,instr(mail_id,'@')+1) d

from tab1),

d2 as (select m,d,n,instr(n,'.',1,1) f_dot,

instr(n,'.',1,2) s_dot from d1)

select m,d,n,f_dot,s_dot,substr(n,1,decode(f_dot,0,length(n),f_dot)) fname,

decode(s_dot,0,null,substr(n,f_dot+1,s_dot-f_dot-1)),

decode(f_dot+s_dot,0,null,substr(n,decode(s_dot,0,f_dot,s_dot)))

from d2

;

 

with d1 as (select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n,

 substr(mail_id,instr(mail_id,'@')+1) d

from tab1),

d2 as (select m,d,n,instr(n,'.',1,1) f_dot,

instr(n,'.',1,2) s_dot from d1)

select m,d,n,f_dot,s_dot,substr(n,1,decode(f_dot,0,length(n),f_dot)) fname,

decode(s_dot,0,null,substr(n,f_dot+1,s_dot-f_dot-1)),

decode(f_dot+s_dot,0,null,substr(n,decode(s_dot,0,f_dot,s_dot)+1)) l_name

from d2

;

with d1 as (select mail_id m,

 substr(mail_id,1, instr(mail_id,'@')-1) n,

 substr(mail_id,instr(mail_id,'@')+1) d

from tab1),

d2 as (select m,d,n,instr(n,'.',1,1) f_dot,

instr(n,'.',1,2) s_dot from d1),

d3 as (select m,d,n,f_dot,s_dot,

substr(n,1,decode(f_dot,0,length(n),f_dot-1)) f_name,

decode(s_dot,0,null,substr(n,f_dot+1,s_dot-f_dot-1))m_name,

decode(f_dot+s_dot,0,null,substr(n,decode(s_dot,0,f_dot,s_dot)+1)) l_name

from d2)

select d,f_name,m_name,l_name from d3;