先贴代码
create or replace type t_table1 is table of VARCHAR2(200)
CREATE OR REPLACE FUNCTION F_SPLIT(P_LIST CLOB, P_SEP VARCHAR2 := ',')
RETURN t_table1
PIPELINED
-- ============================================================
-- 返回字符串被指定字符分割后的表类型
-- ============================================================
IS
L_IDX PLS_INTEGER;
V_LIST VARCHAR2(4000) := 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
IF V_LIST <> '' OR V_LIST IS NOT NULL THEN
PIPE ROW(V_LIST);
END IF;
EXIT;
END IF;
END LOOP;
END;
测试
1.默认的分隔符为“，”
SELECT * FROM TABLE(F_SPLIT('aaaa,bbbb,ccc,ddd,12,ds,cd'));
2.以@为分隔符
SELECT * FROM TABLE(F_SPLIT('aaaa,bbbb,ccc,ddd,12,ds,cd','@'));
3.以@为分隔符
SELECT * FROM TABLE(F_SPLIT('aaaa@bbbb@ccc@ddd@12@ds@cd','@'));
