Oracle 逗號分隔欄位轉列（Split String to Rows）高效 SQL 實作

將單一欄位內的逗號分隔值（如 '1,2,3'）拆分為多列，是 ETL、報表、權限解析常見需求。本文提供純 SQL 解法，利用 CONNECT BY LEVEL 產生序列、INSTR/SUBSTR 切分字串，無需 PL/SQL 迴圈，效能優於 XMLTABLE 或 遞迴 CTE。

核心 SQL（可直接套用）

WITH src AS (
    SELECT 'aaa' AS col_a, 'bbb' AS col_b, '1,2,3' AS col_c FROM dual
    -- 實際使用時替換為：SELECT col_a, col_b, col_c FROM your_table
),
norm AS (
    SELECT col_a, col_b,
           ',' || col_c || ',' AS c_str,           -- 前後加逗號統一邊界
           LENGTH(col_c || ',') - LENGTH(REPLACE(col_c, ',', '')) AS cnt  -- 逗號數 = 元素數-1
    FROM src
)
SELECT col_a, col_b,
       SUBSTR(c_str,
              INSTR(c_str, ',', 1, lvl) + 1,
              INSTR(c_str, ',', 1, lvl + 1) - INSTR(c_str, ',', 1, lvl) - 1) AS col_c_split
FROM norm,
     (SELECT LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 100) gen  -- 100 為單欄位最大拆分數，視需求調整
WHERE lvl <= cnt + 1;  -- 元素數 = 逗號數 + 1

執行結果示例

COL_A  COL_B  COL_C_SPLIT
-----  -----  -----------
aaa    bbb    1
aaa    bbb    2
aaa    bbb    3

關鍵技巧解析

1. 前後加逗號： ',' || col_c || ',' 使首尾元素同樣被逗號包裹，INSTR 邏輯統一
2. 計算元素數： LENGTH(col_c || ',') - LENGTH(REPLACE(col_c, ',')) 等於逗號出現次數，元素數 = 逗號數 + 1
3. CONNECT BY LEVEL 產生序列： 偽表 dual 產生 1..N 序列，N 設為業務上限（如 100、1000）
4. SUBSTR + 雙 INSTR 切分： 第 N 個逗號後 1 字元起，長度 = 第 N+1 個逗號位置 - 第 N 個逗號位置 - 1

進階：支援空元素、去除空白、轉型

WITH src AS (
    SELECT 'a' AS id, ' 1 , 2 , , 3 ' AS val FROM dual
),
norm AS (
    SELECT id,
           ',' || TRIM(val) || ',' AS c_str,
           REGEXP_COUNT(TRIM(val), ',') + 1 AS cnt
    FROM src
)
SELECT id,
       TRIM(SUBSTR(c_str,
                    INSTR(c_str, ',', 1, lvl) + 1,
                    INSTR(c_str, ',', 1, lvl + 1) - INSTR(c_str, ',', 1, lvl) - 1)) AS val_split
FROM norm,
     (SELECT LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 100)
WHERE lvl <= cnt
  AND TRIM(SUBSTR(c_str,
                    INSTR(c_str, ',', 1, lvl) + 1,
                    INSTR(c_str, ',', 1, lvl + 1) - INSTR(c_str, ',', 1, lvl) - 1)) IS NOT NULL;

替代方案比較（12c+）

效能建議

• 單次拆分 ≤ 100 元素：本文寫法極快，CPU 成本極低
• 大量列（百萬級）拆分：考慮建立 PIPELINED FUNCTION 封裝邏輯，搭配 PARALLEL_ENABLE 並行處理
• 避免在高頻 OLTP 查詢中即時拆分，建議 ETL 階段預拆分存入正規化表

字串拆分看似瑣碎，實則考驗 SQL 基礎功力。掌握 INSTR/SUBSTR/CONNECT BY 組合拳，應對 90% 拆分場景綽綽有餘。