Oracle function implementation returns a dataset

/******************* Method 1 ***************** ************/
-- Create type
Create or replace type t_str is table of varchar2 (2000 );
-- Create function
Create or replace function uf_splitstr (I _str varchar2, I _split varchar2)
Return t_str
Pipelined is
Rochelle STR t_str: = t_str ();
L_tmpstr varchar2 (2000): = '';
Rochelle char varchar2 (1 );
Begin
If I _str = ''then
Return;
End if;
For I in 1 .. length (I _str)
Loop
Rochelle CHAR: = substr (I _str, I, 1 );
If l_char = I _split then
Pipe row (l_tmpstr );
Rochelle tmpstr: = '';
Else
L_tmpstr: = l_tmpstr | l_char;
End if;
End loop;
 
Pipe row (l_tmpstr );
 
Return;
End;
-- Example
Select * from table (uf_splitstr ('abcd, asdfad, adfadf ,',','))
/******************* Method 2 ***************** ************/
-- Directly use SQL
With t as (select ', aa 'str,', 'sep from dual)
Select substr (t. str,
T2.l,
Decode (instr (t. str, sep, l ),
0,
Length (t. str) + 1,
Instr (t. str, sep, l)-t2.l)
From t,
(Select level l from dual, t connect by rownum <= length (t. str) + 1) t2
Where substr (sep | t. str, l, 1) = sep


Author 12hao-Zhang Jian