SQL 實現 SPLIT 功能：內嵌表值函數與動態 SQL 批次插入技巧

分割字串並批次插入是常見 ETL 需求。本文提供內嵌表值函數 (高效能) 與動態 SQL 組合插入 (簡潔) 兩種方案。

方案一：內嵌表值函數 (Inline TVF) - 推薦生產使用

CREATE FUNCTION dbo.fn_Split
(
    @SourceSql VARCHAR(8000),
    @StrSeprate VARCHAR(10)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    WITH SplitCTE AS
    (
        SELECT 
            1 AS ItemIndex,
            CASE 
                WHEN CHARINDEX(@StrSeprate, @SourceSql) = 0 THEN @SourceSql
                ELSE LEFT(@SourceSql, CHARINDEX(@StrSeprate, @SourceSql) - 1)
            END AS Item,
            CASE 
                WHEN CHARINDEX(@StrSeprate, @SourceSql) = 0 THEN ''
                ELSE SUBSTRING(@SourceSql, CHARINDEX(@StrSeprate, @SourceSql) + LEN(@StrSeprate), 8000)
            END AS Remainder
        UNION ALL
        SELECT 
            ItemIndex + 1,
            CASE 
                WHEN CHARINDEX(@StrSeprate, Remainder) = 0 THEN Remainder
                ELSE LEFT(Remainder, CHARINDEX(@StrSeprate, Remainder) - 1)
            END,
            CASE 
                WHEN CHARINDEX(@StrSeprate, Remainder) = 0 THEN ''
                ELSE SUBSTRING(Remainder, CHARINDEX(@StrSeprate, Remainder) + LEN(@StrSeprate), 8000)
            END
        FROM SplitCTE
        WHERE Remainder <> '' OR CHARINDEX(@StrSeprate, Remainder) > 0
    )
    SELECT ItemIndex, Item FROM SplitCTE
    OPTION (MAXRECURSION 0)
);

使用方式

' 直接查詢
SELECT * FROM dbo.fn_Split('ABC:BC:C:D:E', ':');

' 批次插入
INSERT INTO TargetTable (ColName)
SELECT Item FROM dbo.fn_Split('1,12,1212,4545', ',');

方案二：動態 SQL 組合 UNION ALL (臨時/簡易場景)

DECLARE @s VARCHAR(8000) = '1,12,1212,4545,454';
DECLARE @sql NVARCHAR(MAX);

SET @sql = 'INSERT INTO #TmpTable (Col) SELECT ' 
         + REPLACE(@s, ',', ' Col UNION ALL SELECT ');

EXEC sp_executesql @sql;

SELECT * FROM #TmpTable;

方案三：遞迴 CTE (無需建立物件、即時使用)

DECLARE @str VARCHAR(8000) = '1,12,1212,4545', @sep VARCHAR(10) = ',';

WITH SplitCTE AS
(
    SELECT 
        1 AS Id,
        LEFT(@str, CASE WHEN CHARINDEX(@sep, @str) = 0 THEN LEN(@str) ELSE CHARINDEX(@sep, @str) - 1 END) AS Val,
        CASE WHEN CHARINDEX(@sep, @str) = 0 THEN '' ELSE SUBSTRING(@str, CHARINDEX(@sep, @str) + 1, 8000) END AS Rest
    UNION ALL
    SELECT 
        Id + 1,
        LEFT(Rest, CASE WHEN CHARINDEX(@sep, Rest) = 0 THEN LEN(Rest) ELSE CHARINDEX(@sep, Rest) - 1 END),
        CASE WHEN CHARINDEX(@sep, Rest) = 0 THEN '' ELSE SUBSTRING(Rest, CHARINDEX(@sep, Rest) + 1, 8000) END
    FROM SplitCTE
    WHERE Rest <> ''
)
SELECT Id, Val FROM SplitCTE
OPTION (MAXRECURSION 0);

效能對比 (10 萬字元、5000 元素測試)

進階：支援多字元分隔符、保留空元素

CREATE FUNCTION dbo.fn_Split_Advanced
(
    @Source VARCHAR(MAX),
    @Sep VARCHAR(10),
    @KeepEmpty BIT = 0
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    WITH SplitCTE AS
    (
        SELECT 
            1 AS Id,
            CASE 
                WHEN CHARINDEX(@Sep, @Source) = 0 THEN @Source
                ELSE LEFT(@Source, CHARINDEX(@Sep, @Source) - 1)
            END AS Val,
            CASE 
                WHEN CHARINDEX(@Sep, @Source) = 0 THEN ''
                ELSE SUBSTRING(@Source, CHARINDEX(@Sep, @Source) + LEN(@Sep), 8000)
            END AS Rest
        UNION ALL
        SELECT 
            Id + 1,
            CASE 
                WHEN CHARINDEX(@Sep, Rest) = 0 THEN Rest
                ELSE LEFT(Rest, CHARINDEX(@Sep, Rest) - 1)
            END,
            CASE 
                WHEN CHARINDEX(@Sep, Rest) = 0 THEN ''
                ELSE SUBSTRING(Rest, CHARINDEX(@Sep, Rest) + LEN(@Sep), 8000)
            END
        FROM SplitCTE
        WHERE Rest <> '' OR (@KeepEmpty = 1 AND CHARINDEX(@Sep, Rest) >= 0)
    )
    SELECT Id, Val FROM SplitCTE
    OPTION (MAXRECURSION 0)
);

選擇指南

• 生產系統、高頻呼叫：部署內嵌 TVF (fn_Split)，SCHEMABINDING 允許計畫重用、優化器內聯展開
• Ad-hoc 查詢、一次性腳本：遞迴 CTE 即時使用，無物件依賴
• 簡單批次插入、資料量小：動態 SQL REPLACE + UNION ALL 最簡潔
• SQL Server 2016+：直接用 STRING_SPLIT (無順序) 或 OPENJSON (有順序、有索引)

內嵌表值函數是 T-SQL 分割字串的**效能王者**，配合 SCHEMABINDING 與 OPTION (MAXRECURSION 0) 可支援任意長度輸入。