Convert a string to an array in Oracle

Converts a string to an array in Oracle and returns the array members at the specified position as needed.

Converts a string to an array in Oracle and returns the array members at the specified position as needed.

Converts a string to an array in Oracle and returns the array members at the specified position as needed.

Create or replace function GetElementFromArrayByIndex (Liststr in varchar2, sPlitVal in varchar2, iPos integer) return varchar2 is
/*
Liststr -- input the string to be split
SPlitVal -- string to be separated
IPos -- get the element value of this position in the split Array

*/
Type tt_type is table of varchar2 (100) index by BINARY_INTEGER;
V1 tt_type;
-- Convert FieldNames to an array
TmpStr varchar2 (100 );
Str varchar2 (4000 );
J integer;
Begin
Str: = Liststr;
J: = 0;
IF Instr (Liststr, sPlitVal, 1, 1) = 0 THEN
V1 (j): = Liststr;
J: = j + 1;
Else
While Instr (str, sPlitVal, 1, 1)> 0 Loop
TmpStr: = Substr (str, 1, Instr (str, sPlitVal, 1, 1)-1 );

V1 (j): = TmpStr;
Str: = SubStr (Str, Instr (str, sPlitVal, 1, 1) + length (sPlitVal), length (str ));
J: = j + 1;
End loop;
If not str is null then
-- Save the last one
V1 (j): = str;
J: = j + 1;
End if;
End if;
If iPos> J-1 or iPos <0 then
-- Exceeds the array Length
Return '';
End if;
Return V1 (ipos );
End;

,