数据库切割字符串函数

1、定义数组类型

--执行下列sql创建数组类型
CREATE OR REPLACE TYPE type_split AS TABLE OF VARCHAR2 (500)

2、创建切割函数

CREATE OR REPLACE FUNCTION STR_SPLIT (p_list VARCHAR2, p_sep VARCHAR2)
    RETURN type_split
    PIPELINED
IS
    l_idx    PLS_INTEGER;
    v_list   VARCHAR2 (500) := p_list;
BEGIN
    LOOP
        l_idx := INSTR (v_list, p_sep);

        IF l_idx > 0
        THEN
            PIPE ROW (SUBSTR (v_list, 1, l_idx - 1));

            v_list := SUBSTR (v_list, l_idx + LENGTH (p_sep));
        ELSE
            PIPE ROW (v_list);

            EXIT;
        END IF;
    END LOOP;

    l_idx := 0;
    v_list := NULL;
    RETURN;
END STR_SPLIT;

3、测试函数

-- CREATE OR REPLACE TYPE type_split AS TABLE OF VARCHAR2 (500)
SELECT
    COLUMN_VALUE AS TARGET_CODE 
FROM
    TABLE(STR_SPLIT ( '13123,34,32,13123,321', ',' ) )