SQL Server 字串分割函數大全：五種 Split 實作與效能比較

前言

SQL Server 缺乏內建 `SPLIT` 函數 (直到 2016 `STRING_SPLIT`)，開發者常需自行實作將分隔字串 (如 'A/B/C') 拆解為資料列或取得指定位置片段。本文整理五種常見實作，含標量函數、表值函數、遞迴 CTE，並分析適用場景。

需求場景

• 欄位儲存路徑/分類代碼：'中國/湖南省/長沙市/雨花區' → 取第 3 層 '長沙市'。
• 前端傳入逗號分隔 ID 清單：'1,2,3,5' → 轉為資料表變數 `JOIN` 查詢。
• 固定格式字串解析：'[ad][ba][daf]' → 拆解為三列。

方案一：遞迴標量函數 `F_split` (取指定位置)

CREATE FUNCTION dbo.F_split (
    @s VARCHAR(8000),   -- 來源字串
    @pos INT,           -- 要取得的片段位置 (1-based)
    @split VARCHAR(10)  -- 分隔符
) RETURNS VARCHAR(100)
AS
BEGIN
    IF @s IS NULL RETURN NULL;
    DECLARE @splitlen INT = LEN(@split + 'a') - 2;  -- 分隔符實際長度
    WHILE @pos > 1 AND CHARINDEX(@split, @s + @split) > 0
        SELECT @pos = @pos - 1,
               @s = STUFF(@s, 1, CHARINDEX(@split, @s + @split) + @splitlen, '');
    RETURN NULLIF(LEFT(@s, CHARINDEX(@split, @s + @split) - 1), '');
END
GO

-- 使用：取第 2 段
SELECT dbo.F_split('aa/bb/cc/dd', 2, '/');  -- 回傳 'bb'

✅ 適合：**單一值提取**、不需拆解全部分段。
⚠️ 迴圈逐字元操作，長字串/高頻呼叫效能較差。

方案二：標量函數 `splitString` (字元分隔符，取指定位置)

CREATE FUNCTION dbo.splitString (
    @string VARCHAR(MAX),
    @delimiter CHAR(1),
    @rep INT = 1
) RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @start INT = 1, @end INT = CHARINDEX(@delimiter, @string), @ic INT = 1;
    WHILE @start < LEN(@string) + 1
    BEGIN
        IF @ic = @rep RETURN SUBSTRING(@string, @start, @end - @start);
        IF @end = 0 SET @end = LEN(@string) + 1;
        SET @start = @end + 1;
        SET @end = CHARINDEX(@delimiter, @string, @start);
        SET @ic = @ic + 1;
    END
    RETURN NULL;
END
GO

-- 使用：分隔符為 ']'，取前 3 段並補回 ']'
SELECT dbo.splitString(col, ']', 1) + ']' AS c1,
       dbo.splitString(col, ']', 2) + ']' AS c2,
       dbo.splitString(col, ']', 3) + ']' AS c3
FROM (VALUES ('[ad][ba][daf]'), ('[erf][afda][dasfasdf]'), ('[fas][fase][reb]')) t(col);

✅ 適合：**單字元分隔符**、固定取前 N 段轉欄位。
⚠️ 僅支援單字元分隔符，回傳 `VARCHAR(10)` 可能截斷。

方案三：內嵌表值函數 `Split` (回傳資料表，集合操作)

CREATE FUNCTION dbo.Split (
    @ItemList VARCHAR(4000),
    @delimiter VARCHAR(10)
) RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
    WHILE CHARINDEX(@delimiter, @ItemList) > 0
    BEGIN
        INSERT @IDTable SELECT LEFT(@ItemList, CHARINDEX(@delimiter, @ItemList) - 1);
        SET @ItemList = STUFF(@ItemList, 1, CHARINDEX(@delimiter, @ItemList), '');
    END
    INSERT @IDTable SELECT @ItemList;
    RETURN;
END
GO

-- 使用：直接 JOIN
SELECT * FROM dbo.Split('aaaa,bbb,ccc,eee', ',');

✅ 適合：**集合操作** (JOIN/IN/APPLY)、回傳所有片段。
⚠️ 多語句表值函數 (MSTVF) 在舊版 Cardinality Estimator 下優化受限，大量資料建議改用內嵌表值函數 (ITVF) 或 `STRING_SPLIT`。

方案四：表值函數 `fn_split` (回傳資料表，支援多字元分隔符)

CREATE FUNCTION dbo.fn_split (
    @inputstr VARCHAR(8000),
    @seprator VARCHAR(10)
) RETURNS @temp TABLE (a VARCHAR(200))
AS
BEGIN
    SET @inputstr = LTRIM(RTRIM(@inputstr));
    DECLARE @i INT = CHARINDEX(@seprator, @inputstr);
    WHILE @i >= 1
    BEGIN
        INSERT @temp VALUES (LEFT(@inputstr, @i - 1));
        SET @inputstr = SUBSTRING(@inputstr, @i + 1, LEN(@inputstr) - @i);
        SET @i = CHARINDEX(@seprator, @inputstr);
    END
    IF @inputstr <> '' INSERT @temp VALUES (@inputstr);
    RETURN;
END
GO

SELECT * FROM dbo.fn_split('1,2,3,4,5,6,7,8,55', ',');

✅ 適合：**多字元分隔符**、需完整拆解所有片段。
⚠️ 同為 MSTVF，效能同上。

方案五：高效內嵌表值函數 `f_splitSTR` (數字表/計數器法，SQL 2008+)

-- 需預先建立計數器表 (一次建立永久使用)
CREATE TABLE dbo.tb_splitSTR (ID INT PRIMARY KEY);
WITH N AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM N WHERE n < 8000)
INSERT dbo.tb_splitSTR SELECT n FROM N OPTION (MAXRECURSION 0);

CREATE FUNCTION dbo.f_splitSTR (
    @s VARCHAR(8000),
    @split VARCHAR(10)
) RETURNS TABLE
AS
RETURN (
    SELECT CAST(SUBSTRING(@s, ID, CHARINDEX(@split, @s + @split, ID) - ID) AS VARCHAR(100)) AS col
    FROM dbo.tb_splitSTR
    WHERE ID <= LEN(@s + 'a')
      AND CHARINDEX(@split, @split + @s, ID) = ID
);
GO

-- 使用
SELECT * FROM dbo.f_splitSTR('A/B/C/D/E', '/');

✅ **效能最佳**：集合式運算、無迴圈、優化器可產生平行計畫。
✅ 內嵌表值函數 (ITVF) 可直接參與查詢最佳化。
⚠️ 需維護輔助計數器表 `tb_splitSTR`。

效能比較與選型建議

SQL Server 2016+ 內建 `STRING_SPLIT`

SELECT value FROM STRING_SPLIT('A,B,C,D', ',');
-- 回傳單欄位資料表，不保證順序 (可加 ORDER BY (SELECT NULL) 配合 ROW_NUMBER)

⚠️ `STRING_SPLIT` **不保證輸出順序**，若順序關鍵請自行加序號或使用 `OPENJSON` (需相容性 130+)。