SQL Server 字串拆分三大經典方法效能實測與比較

將逗號分隔的字串（如 'ak47,mp5,1,23'）拆分為多行資料，是 T-SQL 開發中的高頻需求。本文介紹三種不同思路的實作方法，並從可讀性、效能、適用場景三個維度進行深度比較。

測試資料準備

USE tempdb;
GO
DECLARE @s VARCHAR(1000) = 'ak47,mp5,1,23';
/* 預期輸出：
S
----
ak47
mp5
1
23
*/

方法一：動態 SQL 拼接執行（樸實派）

透過 REPLACE 將分隔符轉換為 UNION SELECT 陳述式，再用 EXEC 執行。

DECLARE @s1 VARCHAR(1000);
SET @s1 = RIGHT(REPLACE(',' + @s, ',', ''' AS S UNION SELECT '''), 
                LEN(REPLACE(',' + @s, ',', ''' AS S UNION SELECT ''')) - 12) + '''';
EXEC(@s1);

優點：邏輯直觀，無需輔助表。
缺點：動態 SQL 有注入風險，編譯開銷大，難以參數化，不適合高頻呼叫。

方法二：系統表交叉聯接產生數字序列（變通派）

利用 sysobjects 產生連續整數序列，配合 CHARINDEX/SUBSTRING 切割字串。

SELECT REPLACE(REVERSE(LEFT(s, CHARINDEX(',', s))), ',', '') AS S
FROM (
    SELECT r, REVERSE(LEFT(@s, r)) + ',' AS s
    FROM (
        SELECT (SELECT COUNT(*) FROM sysobjects WHERE name <= t.name) AS r
        FROM sysobjects t
    ) a
    WHERE r <= LEN(@s)
      AND LEFT(@s + ',', r + 1) LIKE '%,'
) t
ORDER BY r;

優點：純集合運算，無動態 SQL。
缺點：依賴系統表行數，資料量大時效能差，邏輯晦澀難維護。

方法三：XML 原生解析（高級派，推薦）

將字串轉為 XML 結構，再用 OPENXML 或 nodes() 方法撕析（此處示範 OPENXML）。

DECLARE @idoc INT, @doc XML;
SET @doc = CAST('' + REPLACE(@s, ',', '') + '' AS XML);
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
SELECT * FROM OPENXML(@idoc, '/Root/item', 2) WITH ([S] VARCHAR(10));
EXEC sp_xml_removedocument @idoc;

優點：語法標準、效能穩定、支援複雜型別轉換、無注入風險。
現代寫法：SQL Server 2005+ 建議直接用 @doc.nodes('/Root/item') 替代 OPENXML，無需管理文件控制代碼。

效能與選型建議

結論：生產環境請優先使用 XML 方案（或 2016+ 的 STRING_SPLIT 內建函數），兼顧效能、安全與可維護性。