Oracle string segmentation

1. Create an array first

Create or replace type t_ret_table is table of varchar2 (20 );

2. Use a custom function to implement 1. Use the function to return an array.
Create or replace function f_split_string (var_str in string, var_split In String) return t_ret_table
Is
Var_out t_ret_table;
Var_tmp varchar2 (4000 );
Var_element varchar2 (4000 );
Begin
Var_tmp: = var_str;
Var_out: = t_ret_table ();
-- If a matched delimiter exists
While instr (var_tmp, var_split)> 0 loop
Var_element: = substr (var_tmp, 1, instr (var_tmp, var_split)-1 );
Var_tmp: = substr (var_tmp, instr (var_tmp, var_split) + length (var_split), 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_split_string;

3. Use the pipelined Function.
Create or replace function f_split (var_str in string, var_split In String) return t_ret_table PIPELINED
As
Var_tmp varchar2 (4000 );
Var_element varchar2 (4000 );
N_length Number: = length (var_split );
Begin
Var_tmp: = var_str;
While instr (var_tmp, var_split)> 0 loop
Var_element: = substr (var_tmp, 1, instr (var_tmp, var_split)-1 );
Var_tmp: = substr (var_tmp, instr (var_tmp, var_split) + n_length, length (var_tmp ));
Pipe row (var_element );
End loop;
Pipe row (var_tmp );
Return;
End f_split;

1 2 Next Page