Oracle 逗號分隔欄位轉列（行）單一 SQL 實作：CONNECT BY + 字串切割技巧

業務場景

單一欄位以逗號儲存多值（如權限代碼 'READ,WRITE,EXEC'），需查詢「擁有 WRITE 權限的使用者」。
LIKE '%,WRITE,%' 效能差且易誤判（如 WRITE_ADMIN），最佳解法是將逗號分隔字串展開為多列，再以等值比對或 JOIN。

核心 SQL（Oracle 專用）

SELECT a, b,
       SUBSTR(t.ca, INSTR(t.ca, ',', 1, c.lv) + 1,
              INSTR(t.ca, ',', 1, c.lv + 1) - INSTR(t.ca, ',', 1, c.lv) - 1) AS c
FROM (
    SELECT a, b,
           ',' || c || ',' AS ca,                 -- 前後加逗號便於統一切割
           LENGTH(c || ',') - NVL(LENGTH(REPLACE(c, ',', '')), 0) AS cnt
    FROM test
) t,
(
    SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100  -- 最大切割段數，視實際調整
) c
WHERE c.lv <= t.cnt;

原理拆解

1. ',' || c || ','：確保首尾皆有分隔符，統一切割邏輯。
2. LENGTH(c||',') - LENGTH(REPLACE(c,',',''))：計算逗號個數 = 切割段數。
3. CONNECT BY LEVEL <= 100：產生 1~100 序號，作為切割迴圈計數器。
4. INSTR(..., 1, lv)：找第 lv 個逗號位置，配合 SUBSTR 取中間值。

實戰應用：權限查詢

-- 原表：user_perm(user_id, perm_codes)  perm_codes 如 '101,102,105'
-- 需求：找出擁有權限 102 的使用者

WITH expanded AS (
    SELECT user_id,
           TO_NUMBER(
               SUBSTR(t.ca, INSTR(t.ca, ',', 1, c.lv) + 1,
                      INSTR(t.ca, ',', 1, c.lv + 1) - INSTR(t.ca, ',', 1, c.lv) - 1)
           ) AS perm_code
    FROM (
        SELECT user_id, ',' || perm_codes || ',' AS ca,
               LENGTH(perm_codes || ',') - NVL(LENGTH(REPLACE(perm_codes, ',', '')), 0) AS cnt
        FROM user_perm
    ) t,
    (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 50) c
    WHERE c.lv <= t.cnt
)
SELECT DISTINCT user_id FROM expanded WHERE perm_code = 102;

效能與替代方案對比

XMLTABLE 寫法（11g+ 推薦）

SELECT user_id, TO_NUMBER(COLUMN_VALUE) AS perm_code
FROM user_perm,
     XMLTABLE(('"' || REPLACE(perm_codes, ',', '","') || '"'));

最佳實踐建議

• 若系統長期需此類查詢，務必正規化建立關聯表，避免違反第一正規化（1NF）。
• 既有系統無法重構時，建議建立 Materialized View 或 Function-Based Index 於切割邏輯上（12c+ 支援 JSON 索引）。
• CONNECT BY LEVEL <= N 中的 N 設為業務上可能的最大值+安全邊際，避免資料遺漏。