T-SQL 實作字串分割函數：將分隔字串轉換為資料表值函數 (TVF)

SQL Server 2016 前無內建 STRING_SPLIT，且 STRING_SPLIT 不保證順序、無序號。自訂資料表值函數 (TVF) 可精確控制分割邏輯、保留順序、支援多字元分隔符，是 T-SQL 開發必備工具函數。

完整函數定義

CREATE FUNCTION dbo.SplitStringToTable
(
    @InputString NVARCHAR(MAX),      -- 待分割字串
    @Separator   NVARCHAR(10) = N',' -- 分隔符，預設逗號
)
RETURNS @Result TABLE
(
    Seq INT IDENTITY(1,1) PRIMARY KEY,  -- 順序號
    Value NVARCHAR(MAX) NOT NULL        -- 分割後片段
)
AS
BEGIN
    DECLARE @StartPos INT = 1,
            @FindPos  INT,
            @Len      INT = LEN(@InputString),
            @SepLen   INT = LEN(@Separator),
            @Token    NVARCHAR(MAX);

    -- 空字串直接返回空表
    IF @Len = 0 RETURN;

    WHILE @StartPos <= @Len
    BEGIN
        -- 尋找下一個分隔符位置
        SET @FindPos = CHARINDEX(@Separator, @InputString, @StartPos);
        
        -- 找不到分隔符，取剩餘字串
        IF @FindPos = 0 
            SET @FindPos = @Len + 1;

        -- 擷取片段
        SET @Token = SUBSTRING(@InputString, @StartPos, @FindPos - @StartPos);
        
        -- 插入結果表
        INSERT INTO @Result (Value) VALUES (@Token);
        
        -- 移動起始位置 (跳過分隔符)
        SET @StartPos = @FindPos + @SepLen;
    END

    RETURN;
END;
GO

測試與驗證

-- 基本測試
SELECT * FROM dbo.SplitStringToTable(N'a,b,c,d,e,f,g', N',');

-- 多字元分隔符
SELECT * FROM dbo.SplitStringToTable(N'Apple||Orange||Banana', N'||');

-- 連續分隔符 (產生空字串)
SELECT * FROM dbo.SplitStringToTable(N'a,,b', N',');

-- 開頭/結尾分隔符
SELECT * FROM dbo.SplitStringToTable(N',a,b,', N',');

-- 無分隔符
SELECT * FROM dbo.SplitStringToTable(N'HelloWorld', N',');

-- 空字串
SELECT * FROM dbo.SplitStringToTable(N'', N',');

輸出範例

進階增強版：支援去除空值、修剪空白

CREATE FUNCTION dbo.SplitStringToTableEx
(
    @InputString NVARCHAR(MAX),
    @Separator   NVARCHAR(10) = N',',
    @TrimSpace   BIT = 1,      -- 是否 TRIM 空白
    @RemoveEmpty BIT = 1       -- 是否移除空值
)
RETURNS @Result TABLE (Seq INT IDENTITY(1,1) PRIMARY KEY, Value NVARCHAR(MAX) NOT NULL)
AS
BEGIN
    DECLARE @StartPos INT = 1, @FindPos INT, @Len INT = LEN(@InputString), @SepLen INT = LEN(@Separator), @Token NVARCHAR(MAX);
    IF @Len = 0 RETURN;

    WHILE @StartPos <= @Len
    BEGIN
        SET @FindPos = CHARINDEX(@Separator, @InputString, @StartPos);
        IF @FindPos = 0 SET @FindPos = @Len + 1;
        
        SET @Token = SUBSTRING(@InputString, @StartPos, @FindPos - @StartPos);
        IF @TrimSpace = 1 SET @Token = LTRIM(RTRIM(@Token));
        
        IF @RemoveEmpty = 0 OR @Token <> N''
            INSERT INTO @Result (Value) VALUES (@Token);
        
        SET @StartPos = @FindPos + @SepLen;
    END
    RETURN;
END;
GO

效能考量與替代方案

Tally Table 集合式寫法 (高效能、無迴圈)

CREATE FUNCTION dbo.SplitString_Tally
(
    @InputString NVARCHAR(MAX),
    @Separator   NVARCHAR(10) = N','
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH 
    L0 AS (SELECT 1 AS c UNION ALL SELECT 1),          -- 2^1
    L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),   -- 2^2
    L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),   -- 2^4
    L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),   -- 2^8
    L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),   -- 2^16 = 65536
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L4),
    Separators AS (
        SELECT n AS Pos
        FROM Nums
        WHERE n <= LEN(@InputString) 
          AND SUBSTRING(@InputString, n, LEN(@Separator)) = @Separator
    ),
    Tokens AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY Pos) AS Seq,
            SUBSTRING(
                @InputString, 
                CASE WHEN LAG(Pos) OVER (ORDER BY Pos) IS NULL THEN 1 
                     ELSE LAG(Pos) OVER (ORDER BY Pos) + LEN(@Separator) END,
            CASE WHEN Pos IS NULL THEN LEN(@InputString) + 1 
                 ELSE Pos END 
            - CASE WHEN LAG(Pos) OVER (ORDER BY Pos) IS NULL THEN 1 
                   ELSE LAG(Pos) OVER (ORDER BY Pos) + LEN(@Separator) END
            ) AS Value
        FROM (
            SELECT Pos FROM Separators
            UNION ALL SELECT LEN(@InputString) + 1
        ) AS AllPos(Pos)
    )
SELECT Seq, Value FROM Tokens WHERE Value <> N'' OR @InputString = N'';
GO

實戰應用場景

• 前端傳入多值參數：WHERE ID IN (SELECT Value FROM dbo.SplitStringToTable(@Ids, ','))
• CSV/匯入檔解析：配合 BULK INSERT 逐行分割
• 動態 SQL IN 列表：避免字串拼接注入風險
• 標籤/關鍵字拆解：全文檢索前處理

根據 SQL Server 版本與效能需求選擇：2022+ 用 STRING_SPLIT(..., enable_ordinal=1)；2016-2019 用 Tally Table TVF；舊版用 WHILE TVF 或 XML。自訂 TVF 邏輯可控、好維護，是 T-SQL 開發工具箱核心元件。