SQL Server 2016 前無內建 `STRING_SPLIT`,開發者常自寫分割函數。本文解析原文 `WHILE` 迴圈標量函數邏輯,指出**效能陷阱 (RBAR 逐行處理)**,並提供 **內建 `STRING_SPLIT`、JSON 解析、XML 解析、CLR、數字表** 五大現代高效替代方案。
ALTER FUNCTION dbo.core_split
(
@str VARCHAR(100),
@split CHAR(1),
@index INT
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @count INT = 0, @s VARCHAR(10) = '';
WHILE (CHARINDEX(@split, @str) <> 0)
BEGIN
IF @count = @index
SET @s = SUBSTRING(@str, 1, CHARINDEX(@split, @str) - 1);
SET @str = STUFF(@str, 1, CHARINDEX(@split, @str), ''); -- 移除已處理段
SET @count += 1;
END
IF CHARINDEX(@split, @str) = 0 SET @s = @str; -- 處理最後一段
RETURN @s;
END
-- 回傳資料表值函數 (TVF),可直接 JOIN / CROSS APPLY
SELECT value FROM STRING_SPLIT('aa,dd,cc,rr,fff', ',');
-- 結果:每行一個元素 (順序不保證,SQL 2022+ 支援 enable_ordinal=1 保序)
-- 實戰:將逗號分隔字串轉為行集合
SELECT s.value
FROM MyTable t
CROSS APPLY STRING_SPLIT(t.CsvColumn, ',') s;
-- 將逗號分隔字串包裝為 JSON 陣列
SELECT [value]
FROM OPENJSON('["' + REPLACE('aa,dd,cc,rr,fff', ',', '","') + '"]');
-- 結果:保證順序 (含 key=索引)、自動處理引號轉義、回傳型別 (字串/數值/布林/NULL/物件/陣列)
DECLARE @xml XML = N' ';
SELECT x.value('(./text())[1]', 'VARCHAR(100)')
FROM @xml.nodes('/r') AS T(x);
-- 建立數字表 (一次性建立,永久使用)
CREATE TABLE dbo.Tally (N INT PRIMARY KEY);
INSERT INTO dbo.Tally (N) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b;
-- 分割查詢 (單次掃描,集合操作)
DECLARE @str VARCHAR(8000) = 'aa,dd,cc,rr,fff', @delim CHAR(1) = ',';
SELECT
SUBSTRING(@str, N, CHARINDEX(@delim, @str + @delim, N) - N) AS Token
FROM dbo.Tally
WHERE N <= LEN(@str) + 1
AND (N = 1 OR SUBSTRING(@str, N-1, 1) = @delim)
ORDER BY N;
| 場景 | 推薦方案 | 關鍵優勢 |
|---|---|---|
| SQL 2016+、單純分割、不需順序 | STRING_SPLIT | 原生、最快、語法最簡 |
| SQL 2016+、需保序/型別/處理引號 | OPENJSON | 標準 JSON、保序、型別豐富 |
| SQL 2005-2014、高效能分割 | Tally Table / Numbers Table | 集合操作、無迴圈、全版本通用 |
| 極複雜分割邏輯 (正則、巢狀、多字元分隔符) | CLR TVF | C# 生態全威力、效能最強 |
| 舊系統維護、極小資料量、臨時腳本 | 原 WHILE 函數 / XML | 相容性、快速寫完 |
-- 查找含特定標籤的文章 (標籤存為 CSV)
SELECT a.* FROM Article a
WHERE EXISTS (
SELECT 1 FROM STRING_SPLIT(a.Tags, ',') s WHERE TRIM(s.value) = 'SQL'
);
**標量函數 + WHILE 迴圈分割是效能殺手**,嚴禁用於生產查詢核心路徑。**SQL 2016+ 直接用 `STRING_SPLIT` / `OPENJSON`**;舊版本建立 **永久 Tally Table** 進行集合式分割。將分割邏輯下推至資料庫層 (TVF/CROSS APPLY) 而非應用層迴圈,是關係型思維的體現,亦是高效能的前提。