An example of Oracle separator string

Original is located in: http://www.itpub.net/viewthread.php? Tid = 610297 & extra = page % 3D2% 26amp % 3 Bfilter % 3 Ddigest & page = 2

Splits 'a | B | C' string
A
B
C

Create or replace function f_test (var_str in string) return t_ret_table is
Var_out t_ret_table;
Var_tmp varchar2 (2000 );
Var_element varchar2 (2000 );
Begin
Var_tmp: = var_str;
Var_out: = t_ret_table ();
While instr (var_tmp, '|')> 0 loop
Var_element: = substr (var_tmp, 1, instr (var_str, '|')-1 );
Var_tmp: = substr (var_tmp, instr (var_str, '|') + 3, length (var_tmp ));
Var_out.extend (1 );
Var_out (var_out.count): = var_element;
End loop;
Var_out.extend (1 );
Var_out (var_out.count): = var_tmp;
Return var_out;
End f_test;
/

Set serverout on
Declare
Aa t_ret_table;
Begin
Aa: = f_test ('a | B | C ');
For I in 1... aa. count loop
Dbms_output.put_line (aa (I ));
End loop;
End;
/

Create or replace function f_test (var_str in string) return t_ret_table PIPELINED
As
Var_tmp varchar2 (2000 );
Var_element varchar2 (2000 );
Begin
Var_tmp: = var_str;
While instr (var_tmp, '|')> 0 loop
Var_element: = substr (var_tmp, 1, instr (var_str, '|')-1 );
Var_tmp: = substr (var_tmp, instr (var_str, '|') + 3, length (var_tmp ));
Pipe row (var_element );
End loop;
Pipe row (var_tmp );
Return;
End f_test;
/

Select * from table (f_test ('a | B | C '));