Search the Site


bottom corner

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.

This page is filed under keyword(s): 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
	v_list varchar2(32767) := delimiter || input_list;
	start_position number;
	end_position number;
	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));
		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!

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 19 Jun 2008

bottom corner