Oracle | How to parse full_name
--topic for the day
--how to parse full name into fist namse and last name...
--step 1
create object type
create or replace type test_chk_ot as object (
full_name varchar2(70),
first_name varchar2(30),
last_name varchar2(30)
);
--step 2
create table type
create or replace type test_chk_nt is table of test_chk_ot;
--step 3
create pipeline function
create or replace function fn_parse_name
return test_chk_nt pipelined
as
v_first_name varchar2(30);
v_last_name varchar2(30);
l_name varchar2(70);
cursor c1 is
select full_name from test_chk;
begin
open c1 ;
loop
fetch c1 into l_name;
exit when c1%notfound;
v_first_name := ltrim(regexp_replace(substr(l_name,instr(l_name,',')+1),'[(tet)(mst)]*$','')) ;
v_last_name := substr(l_name,1,instr(l_name,',')-1) ;
pipe row(test_chk_ot(l_name, v_first_name,v_last_name));
end loop;
close c1;
return ;
end;
--step 4
select * from pipeline function and test the result...
select * from table(fn_parse_name());
