Create Custom split() function in Oracle

Some programming and scripting languages provide built-in functions that split a string around a given delimiter. Oracle SQL does not offer similar functionality out of the box, but this following function can help fill the gap.

create or replace function split(input_list varchar2, ret_this_one number, delimiter varchar2) 

return varchar2 is 

        v_list varchar2(32767) := delimiter || input_list; 

        start_position number; 

        end_position number; 

begin 

        start_position := instr(v_list, delimiter, 1, ret_this_one); 

        if start_position > 0 then 

                end_position := instr( v_list, delimiter, 1, ret_this_one + 1); 

                if end_position = 0 then 

                        end_position := length(v_list) + 1; 

                end if; 

return(substr(v_list, start_position + 1, end_position - start_position - 1)); 

        else 

        return NULL; 

        end if; 

end split; 

/ show errors;

In the previous listed example, we would run this function as follows.

select split('AAA,BBB',1,','); -- Returns AAA 

select split('AAA,BBB',2,','); -- Returns BBB

Please note that the first index is 1, not 0.

The following function will take in a list, let's say "AAA,BBB", split them up to "AAA" and "BBB", and allow the user to specify which one to return.