oracle根据特定分隔符单条数据分割为多行

先贴代码

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','@'));