Oracle split function

This function splits a string by the given regex in Oracle 11g

The Type:

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

The Code:

CREATE OR REPLACE  FUNCTION SPLIT
  (
    i_str      IN VARCHAR2,
    i_regex    IN VARCHAR2,
    i_modifier IN VARCHAR2 DEFAULT NULL
  ) RETURN str_array IS
 
    l_str_array str_array;
    l_count     NUMBER;
 
  BEGIN
 
    l_count := regexp_count(i_str,
                            i_regex,
                            1,
                            i_modifier);
 
    SELECT CAST(MULTISET (SELECT CASE
                          WHEN l_count = 0 THEN
                           i_str
                          WHEN LEVEL = l_count + 1 THEN
                           regexp_substr(i_str,
                                         '.*(' || i_regex || ')(.*)',
                                         1,
                                         1,
                                         i_modifier,
                                         2)
                       
                          ELSE
                           regexp_substr(i_str,
                                         '(.*?)(' || i_regex || ')',
                                         1,
                                         LEVEL,
                                         i_modifier,
                                         1)
                        END AS SPLIT
                   FROM dual
                 CONNECT BY LEVEL <= l_count + 1) AS str_array)
      INTO l_str_array
      FROM dual;
 
    RETURN l_str_array;
 
  END;

The Test:

SELECT COLUMN_VALUE, 1 FROM TABLE(SPLIT('1 = 1  AND  2 = 2 AND 3 = 3 AND 4 = 4',  '[[:blank:]]+AND[[:blank:]]+'  ))
UNION ALL
SELECT COLUMN_VALUE, 2 FROM TABLE(SPLIT('x = x',  '[[:blank:]]+AND[[:blank:]]+'  ))
UNION ALL
SELECT COLUMN_VALUE, 3 FROM TABLE(SPLIT('a,b,c,d,e,f,g,h',  ','  ))
UNION ALL
SELECT COLUMN_VALUE, 4 FROM TABLE(SPLIT('there is a dog and another dog!',  'dog'  ))
; 

 The Result:

1 = 1	1
2 = 2	1
3 = 3	1
4 = 4	1
x = x	2
a	3
b	3
c	3
d	3
e	3
f	3
g	3
h	3
there is a	4
and another	4
!	4

Links:

http://www.oracle.com/global/de/community/tipps/regexp/index.html

http://www.oracle-developer.net/display.php?id=508

http://glosoli.blogspot.com/2006/07/oracle-plsql-function-to-split-strings.html

http://sadique-sadiqulislam.blogspot.com/2010/02/split-function-for-oracle-10g.html