Friday, September 3, 2010

split function in ORACLE

http://klauskurz.wordpress.com/2010/06/04/oracle-split-function/

QUOTE:

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

-- type


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



-- 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;
highlighted with: http://tohtml.com/