How to best split csv strings in oracle 9i

csv, oracle, tokenize

Solution

Here's a string tokenizer for Oracle that's a little more straightforward than that page, but no idea if it's as fast:

create or replace function splitter_count(str in varchar2, delim in char) return int as
val int;
begin
  val := length(replace(str, delim, delim || ' '));
  return val - length(str); 
end;

create type token_list is varray(100) of varchar2(200);

CREATE or replace function tokenize (str varchar2, delim char) return token_list as
ret token_list;
target int;
i int;
this_delim int;
last_delim int;
BEGIN
  ret := token_list();
  i := 1;
  last_delim := 0;
  target := splitter_count(str, delim);
  while i <= target
  loop
    ret.extend();
    this_delim := instr(str, delim, 1, i);
    ret(i):= substr(str, last_delim + 1, this_delim - last_delim -1);
    i := i + 1;
    last_delim := this_delim;
  end loop;
  ret.extend();
  ret(i):= substr(str, last_delim + 1);
  return ret;
end;

You can use it like this:

select tokenize('hi you person', ' ') from dual;
VARCHAR(hi,you,person)

Problem

I want to be able to split csv strings in Oracle 9i I've read the following article http://www.oappssurd.com/2009/03/string-split-in-oracle.html But I didn't understand how to make this work. Here are some of my questions pertaining to it - Would this work in Oracle 9i, if not, why not? - Is there a better way of going about splitting csv strings then the solution presented above? - Do I need to create a new type? If so, do I need specific privilages for that? - Can I declare the type w/in the function?

Original source

Related problems