Oracle 字串轉行完全指南：逗點分隔值拆分為多列

核心技術：CONNECT BY + INSTR/SUBSTR

利用 Oracle 階層式查詢 CONNECT BY LEVEL 產生序號，配合 INSTR 定位分隔符位置，SUBSTR 擷取子字串，將單欄位逗點分隔值拆分為多列。

基礎範例：固定字串拆分

SELECT SUBSTR(T.RPT_ID,
                INSTR(T.RPT_ID, ',', 1, C.LV) + 1,
                INSTR(T.RPT_ID, ',', 1, C.LV + 1) - INSTR(T.RPT_ID, ',', 1, C.LV) - 1
               ) AS RPT_ID
FROM (
  SELECT ',' || '85,86,87' || ',' AS RPT_ID,
         LENGTH('85,86,87' || ',') - NVL(LENGTH(REPLACE('85,86,87', ',')), 0) AS CNT
  FROM DUAL
) T,
(
  SELECT LEVEL LV
  FROM DUAL
  CONNECT BY LEVEL <= LENGTH('85,86,87' || ',') - NVL(LENGTH(REPLACE('85,86,87', ',')), 0)
) C
WHERE T.CNT >= C.LV;

關鍵邏輯解析

1. 首尾加分隔符：',' || str || ',' 統一處理首尾元素
2. 計算元素個數：LENGTH(str||',') - NVL(LENGTH(REPLACE(str, ',')), 0)
3. 產生序號：CONNECT BY LEVEL <= 元素個數
4. 定位第 N 個分隔符：INSTR(str, ',', 1, N)
5. 擷取子字串：起始位置 = 第 N 個分隔符 + 1，長度 = 第 N+1 個分隔符 - 第 N 個分隔符 - 1

實戰：資料表欄位拆分

-- 1. 先查最大元素數（用於 CONNECT BY 上限）
SELECT MAX(LENGTH(RPT_ID || ',') - NVL(LENGTH(REPLACE(RPT_ID, ',')), 0)) 
INTO v_max_cnt
FROM DIM_AUDIT_TABLE@sjmh_inter;

-- 2. 正式拆分查詢
SELECT T.T_NAME, T.T_NAME_COMM, T.T_COLUMN, T.T_COLUMN_COMM,
       T.COMMENT_NL, T.COMMENT_NL_TIME, T.SEQ_USER_ID,
       SUBSTR(T.RPT_ID,
              INSTR(T.RPT_ID, ',', 1, C.LV) + 1,
              INSTR(T.RPT_ID, ',', 1, C.LV + 1) - INSTR(T.RPT_ID, ',', 1, C.LV) - 1
             ) AS RPT_ID
FROM (
  SELECT A.T_NAME, A.T_NAME_COMM, A.T_COLUMN, A.T_COLUMN_COMM,
         A.COMMENT_NL, A.COMMENT_NL_TIME, A.SEQ_USER_ID,
         ',' || A.RPT_ID || ',' AS RPT_ID,
         LENGTH(A.RPT_ID || ',') - NVL(LENGTH(REPLACE(A.RPT_ID, ',')), 0) AS CNT
  FROM DIM_AUDIT_TABLE@sjmh_inter A
  WHERE A.COMMENT_NL IS NOT NULL
) T,
(
  SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= v_max_cnt
) C
WHERE C.LV <= T.CNT;

遠端查詢注意事項

若使用 Database Link 查詢遠端表，CONNECT BY LEVEL 的 DUAL 可能導致 只回傳第一列。解決方法：改用 ALL_OBJECTS 或 USER_OBJECTS 代替 DUAL：

SELECT LEVEL LV FROM ALL_OBJECTS CONNECT BY LEVEL <= v_max_cnt

替代方案：XMLTABLE (10g+)

SELECT TRIM(COLUMN_VALUE) AS RPT_ID
FROM XMLTABLE(('"' || REPLACE('85,86,87', ',', '","') || '"'));

替代方案：REGEXP_SUBSTR (10g+)

SELECT REGEXP_SUBSTR('85,86,87', '[^,]+', 1, LEVEL) AS RPT_ID
FROM DUAL
CONNECT BY REGEXP_SUBSTR('85,86,87', '[^,]+', 1, LEVEL) IS NOT NULL;

效能比較建議