How to split Single row value into multiple lines ?
SQL> create table emp_split (
2 id number,
3 name varchar2(30),
4 description varchar2(2000)
5 )
6 ;
Table created.
SQL> insert into emp_split (id,name,description)
2 values(1,'akbar','I am an Oracle Certified Professional, Having good knowdedge in Oracle Database and Developer, I have completed several projects, I
have been working since 3 years')
3 ;
1 row created.
SQL> commit;
Commit complete.
SQL> CREATE OR REPLACE package emb_string as
2 type string_array is table of varchar2(32767);
3 function split_string(str in varchar2, delimiter in char default ',') return string_array;
4 end emb_string;
5 /
Package created.
SQL> CREATE OR REPLACE package body emb_string as
2 function split_string(str in varchar2, delimiter in char default ',') return string_array is
3 return_value string_array := string_array();
4 split_str long default str || delimiter;
5 i number;
6 begin
7 loop
8 i := instr(split_str, delimiter);
9 exit when nvl(i,0) = 0;
10 return_value.extend;
11 return_value(return_value.count) := trim(substr(split_str, 1, i-1));
12 split_str := substr(split_str, i + length(delimiter));
13 end loop;
14 return return_value;
15 end split_string;
16 end emb_string;
17 /
Package body created.
SQL> CREATE OR REPLACE FUNCTION F_split_string(P_text varchar2) RETURN VARCHAR2 is
2 --v_id varchar2(10):=p_id;
3 v_text varchar2(2000):=P_text;
4 r_text varchar2(2000);
5 v_split_string emb_string.string_array;
6 begin
7 v_split_string := emb_string.split_string(P_text);
8 if v_split_string.count > 0 then
9 r_text:=v_split_string(1);
10 for i in 2..v_split_string.count loop
11 r_text:=r_text||chr(10)||v_split_string(i);
12 end loop;
13 end if;
14 return r_text;
15 end;
16 /
Function created.
SQL> select F_split_string(description) from emp_split;
F_SPLIT_STRING(DESCRIPTION)
--------------------------------------------------------------------------------
I am an Oracle Certified Professional
Having good knowdedge in Oracle Database and Developer
I have completed several projects
I have been working since 3 years
Thank you for share
ReplyDelete