Split Function with Regular Expression in Oracle 10g

Few days before, I was looking for split function in oracle (like java/C#), but did not find any. Then at first I find that Oracle 10g introduce regular expression support for string operation. You can visit http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm to know about this.

Then I utilize oracle pipelined function (to know more you can visit http://www.oracle-developer.net/display.php?id=207) and build my own Split(str varchar2, regex_of_token varchar2) which takes two arguments. First of which is the input string and the second one is the regular expression of the token. The two steps to build the method are -

1. Build a custom collection type for return type -
   

   CREATE OR REPLACE TYPE str_array AS TABLE OF varchar2(1024);

2. Then the function body -
   

   CREATE OR REPLACE FUNCTION SYS.split
   ( str IN varchar2, regex in varchar2)
   RETURN str_array PIPELINED IS
   next_token varchar2(1024) := '';
   token_index number := 1;
   BEGIN
   LOOP
       select regexp_substr(str,regex,1,token_index)
          into next_token from dual;
       exit when (next_token is null);
       pipe row(next_token);
       token_index := token_index+1;
   END LOOP;
   RETURN;
   END;
   /

Then to test this function, call the function in the following way-

select * from table(split('10,1812965650,42.09,1234,3,0906102214,sadique,Sadique','([0-9]+(\.[0-9]+)?|([a-z]|[A-Z])+)'))

The first argument is sample string and second one is regular expression for number or alphabet but not alpha-numeric. If you want to split the string around ',' then the query will be -

select * from
table(split('10,1812965650,42.09,1234,3,0906102214,sadique,Sadique','[^,]+'))