[Reprint] Oracle Str Split Function

Create or replace type tb_strSplit as table of varchar2 (4000 );

Create or replace function to_table (pv_str varchar2, pv_split varchar2) return tb_strSplit
As
Ltab tb_strSplit: = tb_strSplit ();
Pos integer: = 0;
Ls varchar2 (4000): = pv_str;
Begin
Pos: = instr (ls, pv_split );
While pos> 0 loop
Ltab. extend;
Ltab (ltab. count): = substr (ls, 1, pos-1 );
Ls: = substr (ls, pos + length (pv_split ));
Pos: = instr (ls, pv_split );
End loop;
Ltab. extend;
Ltab (ltab. count): = ls;
Return ltab;
End;

Set serverout on
Declare
Aa tbl_str;
Begin
Aa: = to_table ('a | B | c ',' | ');
For I in 1... aa. count loop
Dbms_output.put_line (aa (I ));
End loop;
End;
/

----------------------------------------------------------

Create or replace function strToTb (inVStr in string, Please split in varchar) return tb_strSplit PIPELINED
As
V_tmp varchar2 (4000 );
V_element varchar2 (4000 );
Begin

V_tmp: = inVStr;
While instr (v_tmp, batch split)> 0 loop
V_element: = substr (v_tmp, 1, instr (v_tmp, shard split)-1 );
V_tmp: = substr (v_tmp, instr (v_tmp, 1_split) + length (1_split), length (v_tmp ));
Pipe row (v_element );
End loop;

Pipe row (v_tmp );

Return;
End strToTb;
/

Select * from table (strToTb ('a | B | c ',' | '));