Oracle development topic: Implementing the split Function

Function Description: Cut the input string with the specified separator and return a one-dimensional array. Each array element is a substring.

Source code:

Create or replace type ty_str_split is table of varchar2 (4000 );

Create or replace function fn_split (p_str in varchar2, p_delimiter in varchar2)
Return ty_str_split
Is
J INT: = 0;
I INT: = 1;
Len INT: = 0;
Len1 INT: = 0;
STR varchar2 (4000 );
Str_split ty_str_split: = ty_str_split ();
Begin
Len: = length (p_str );
Len1: = length (p_delimiter );

While j <Len
Loop
J: = instr (p_str, p_delimiter, I );

If J = 0
Then
J: = Len;
STR: = substr (p_str, I );
Str_split.extend;
Str_split (str_split.count): = STR;

If I> = Len
Then
Exit;
End if;
Else
STR: = substr (p_str, I, j-I );
I: = J + len1;
Str_split.extend;
Str_split (str_split.count): = STR;
End if;
End loop;

Return str_split;
End fn_split;
/
Create or replace type ty_str_split is table of varchar2 (4000 );

Create or replace function fn_split (p_str in varchar2, p_delimiter in varchar2)
Return ty_str_split
Is
J INT: = 0;
I INT: = 1;
Len INT: = 0;
Len1 INT: = 0;
STR varchar2 (4000 );
Str_split ty_str_split: = ty_str_split ();
Begin
Len: = length (p_str );
Len1: = length (p_delimiter );

While j <Len
Loop
J: = instr (p_str, p_delimiter, I );

If J = 0
Then
J: = Len;
STR: = substr (p_str, I );
Str_split.extend;
Str_split (str_split.count): = STR;

If I> = Len
Then
Exit;
End if;
Else
STR: = substr (p_str, I, j-I );
I: = J + len1;
Str_split.extend;
Str_split (str_split.count): = STR;
End if;
End loop;

Return str_split;
End fn_split;
/

Test:

Declare
Cursor C
Is
Select *
From table (cast (fn_split ('1; 12; 123; 1234; 12345 ','; ') as ty_str_split
)
);

R c % rowtype;
Begin
Open C;

Loop
Fetch C into R;

Exit when C % notfound;
Dbms_output.put_line (R. column_value );
End loop;

Close C;
End;
/

Result:
1
12
123
1234
12345