SQL 技巧：將逗號分隔字串拆分為多行資料的兩種高效寫法

情境

資料表 testc 欄位 name 存放 'a=1,b=2,c=3' 格式字串，需拆分為三列：

name
-----
a=1
b=2
c=3

方法一：UNION ALL 串聯固定欄位 (適用已知固定段數)

SELECT SUBSTR(name, 1, INSTR(name, ',', 1) - 1) AS name FROM testc
UNION ALL
SELECT SUBSTR(name, INSTR(name, ',', 1) + 1, 
       INSTR(name, ',', 1, 2) - INSTR(name, ',', 1) - 1) FROM testc
UNION ALL
SELECT SUBSTR(name, INSTR(name, ',', 1, 2) + 1, 
       LENGTH(name) - INSTR(name, ',', 1, 2)) FROM testc;

• 使用 INSTR 定位分隔符位置，SUBSTR 截取片段。
• 優點：語法直觀、無需額外物件；缺點：段數固定、程式碼重複、效能隨段數線性增長。

方法二：動態列數 + 遞歸/數字表 (適用不定段數)

SELECT SUBSTR(',' || t1.name || ',', 
       INSTR(',' || t1.name || ',', ',', 1, rn) + 1,
       INSTR(',' || t1.name || ',', ',', 1, rn + 1) - INSTR(',' || t1.name || ',', ',', 1, rn) - 1) AS name
FROM testc t1,
     (SELECT ROWNUM rn FROM all_objects WHERE ROWNUM <= 10) t2
WHERE INSTR(',' || t1.name || ',', ',', 1, rn + 1) > 0;

• 前後加分隔符 ',' || name || ',' 統一邊界處理。
• t2 衍生出數字序列 (此處取 all_objects 前 10 列)，rn 代表第 N 個片段。
• WHERE 子句過濾超出實際段數的序號。
• 優點：支援任意段數、單一查詢完成；缺點：需預估最大段數、Oracle 專用語法 (ROWNUM/all_objects)。

SQL Server 對應寫法 (XML 節點法)

DECLARE @str VARCHAR(100) = 'a=1,b=2,c=3';
SELECT Split.a.value('.', 'VARCHAR(100)') AS name
FROM (SELECT CAST('' + REPLACE(@str, ',', '') + '' AS XML) AS Data) AS t
CROSS APPLY Data.nodes('/M') AS Split(a);

SQL Server 2016+ 可直接使用 STRING_SPLIT 內建函數：SELECT value FROM STRING_SPLIT(@str, ',')。