Wednesday, September 26, 2012

How to split Single row value into multiple lines ?

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                                               
                                                                                

1 comment: