Saturday, August 25, 2007

Oracle PL/SQL function to split strings into tokens


Oracle PL/SQL function to split strings into tokens

create or replace function get_token(
the_list varchar2,
the_index number,
delim varchar2 := ','
)
return varchar2
is
start_pos number;
end_pos number;
begin
if the_index = 1 then
start_pos := 1;
else
start_pos := instr(the_list,delim,1,the_index - 1);
if start_pos = 0 then
return null;
else
start_pos := start_pos + length(delim);
end if;
end if;
end_pos := instr(the_list,delim,start_pos,1);
if end_pos = 0 then
return substr(the_list,start_pos);
else
return substr(the_list,start_pos,end_pos - start_pos);
end if;
end get_token;
/
select
get_token('foo,bar,baz',1), -- 'foo'
get_token('foo,bar,baz',3), -- 'baz'
--
get_token('a,,b',2), -- '' (null)
get_token('a,,b',3), -- 'b'
--
get_token('a|b|c',2,'|'), -- 'b'
get_token('a|b|c',4,'|') -- '' (null)
from
dual
/

0 comments: