Oracle PL/SQL function to split strings into tokens

Oracle PL/SQL function to split strings into tokens

I was trying to find how to split or tokenise (tokenize) a string in Oracle SQL. Technically you can do it directly with just instr and substr but your statement becomes very long and hard to read, mainly because you can't reuse the result of an instr. I found this which was interesting but not what I needed, so I wrote my own function to do the job. I'll post it here in the hope that it will be useful to someone.

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; /  

Example usage:

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 /  

Notes

• Remember that the index starts at one not zero just like strings in PL/SQL.
• Empty tokens are counted. You get null if you ask for an empty token.
• You also get null if you ask for an index greater than the number of tokens.
• The delimiter defaults to a comma but you can specify any delimiter. A delimiter of more than one char should work also.

Update 17/5/07
I've added the notes above and colourised the code to make it look prettier. To the commenters below, you're welcome!

Update 12/9/08
This version provided by Anonymous below supports negative indexes, eg

get_token('foo,bar,baz',-1), -- 'baz' get_token('foo,bar,baz',-2), -- 'bar' 

Also check the comments below for a regex based version that can handle quoted csv style fields.

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;      elsif the_index < 0 then         start_pos := instr(the_list, delim, -1, abs(the_index)) + 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;      if the_index < 0 then         end_pos := instr(the_list, delim, start_pos+1, 1);     else         end_pos := instr(the_list, delim, start_pos, 1);     end if;      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;