Oracle 字符串切割/转列

  第一种方式, 简单点:

-- 字符串转列 (:str 为待切串)
SELECT (REGEXP_SUBSTR(:str, '[^,]+', 1, ROWNUM) ) split
  FROM DUAL
CONNECT BY ROWNUM <= (LENGTH(:str) - LENGTH(REGEXP_REPLACE(:str, ',', '')) + 1)
;
-- 或者
WITH TEST AS (
    select :str as str from dual
)
 SELECT REGEXP_SUBSTR(STR, '[^,]+', 1, rownum) split
 FROM TEST
 CONNECT BY rownum <= LENGTH(STR) - LENGTH(REGEXP_REPLACE(STR, ',', '')) + 1
 ;

这里提供测试sql, 大家可以自己试一试执行速度:

-- 随机数
WITH TEST AS (
    SELECT  listagg(tt.aa, ',') within group ( order by tt.aa) str FROM (
      select abs(DBMS_RANDOM.RANDOM) aa, rownum rn 
      from dual connect by ROWNUM<=150
        ) tt
)
 SELECT STR, to_number(REGEXP_SUBSTR(STR, '[^,]+', 1, rownum)) num
 FROM TEST
 CONNECT BY rownum<=LENGTH(STR)-LENGTH(REGEXP_REPLACE(STR,',',''))+1
 ;
 
-- 自然数
WITH TEST AS (
    SELECT  listagg(tt.rn, ',') within group ( order by tt.rn) str FROM (
    select rownum rn from dual connect by ROWNUM<=200
        ) tt
)
 SELECT STR, to_number(REGEXP_SUBSTR(STR, '[^,]+', 1, rownum)) num
 FROM TEST
 CONNECT BY rownum<=LENGTH(STR)-LENGTH(REGEXP_REPLACE(STR,',',''))+1
 ;

  第二种方式:

-- 1. 创建type
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
-- 2. 创建函数
create or replace FUNCTION fn_split(
    p_str       IN VARCHAR2,
    p_delimiter IN VARCHAR2)
  RETURN ty_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;
  -- PIPE ROW 语句用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束
  RETURN;
END fn_split;

测试:

-- 使用 TABLE 操作符进行测试
SELECT * FROM TABLE (fn_split (
(SELECT  listagg(tt.rn, ',') within group ( order by tt.rn) str FROM (
    SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<=1000
        ) tt)
, ','))
;

附测试图



The end.