Custom split() function in Oracle

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.

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.

This function is inspired and based on work done by Simon Baird and Francois Degrelle. Thanks Simon and Francois!