oracle split函数

oracle split函数

 

效率还可以,比以前的做法要高点

 

CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);

CREATE OR REPLACE FUNCTION String_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN str_split PIPELINED
IS
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (4000);
BEGIN
    len := LENGTH (p_str);
    len1 := LENGTH (p_delimiter);

    WHILE j < len
    LOOP
        j := INSTR (p_str, p_delimiter, i);

        IF j = 0
        THEN
            j := len;
            str := SUBSTR (p_str, i);
            PIPE ROW (str);

            IF i >= len
            THEN
                EXIT;
            END IF;
        ELSE
            str := SUBSTR (p_str, i, j - i);
            i := j + len1;
            PIPE ROW (str);
        END IF;
    END LOOP;

    RETURN;
END String_split;
/

测试：

SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));

结果：
1
12
123
1234
12345