SQL Server 自定義分割函數 SplitString：支援多字元分隔符與空值控制

需求

T-SQL 內建 STRING_SPLIT（SQL 2016+）不保證順序、不支援多字元分隔符、無法保留空值。自製 SplitString 解決三大痛點。

函數定義

CREATE FUNCTION dbo.SplitString
(
    @Input           NVARCHAR(MAX),
    @Separator       NVARCHAR(MAX) = N',',
    @RemoveEmptyEntries BIT = 1
)
RETURNS @Result TABLE (Id INT IDENTITY(1,1) PRIMARY KEY, Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @Idx INT, @Token NVARCHAR(MAX);
    SET @Idx = CHARINDEX(@Separator, @Input);

    WHILE @Idx > 0
    BEGIN
        SET @Token = LTRIM(RTRIM(SUBSTRING(@Input, 1, @Idx - 1)));
        IF @RemoveEmptyEntries = 0 OR @Token <> N''
            INSERT INTO @Result(Value) VALUES(@Token);

        SET @Input = SUBSTRING(@Input, @Idx + DATALENGTH(@Separator)/2, LEN(@Input));
        SET @Idx = CHARINDEX(@Separator, @Input);
    END

    -- 最後一段
    SET @Token = LTRIM(RTRIM(@Input));
    IF @RemoveEmptyEntries = 0 OR @Token <> N''
        INSERT INTO @Result(Value) VALUES(@Token);

    RETURN;
END;

測試用例

DECLARE @s1 VARCHAR(MAX) = '1,2,3',
        @s2 VARCHAR(MAX) = '1###2###3',
        @s3 VARCHAR(MAX) = '1###2###3###';

SELECT * FROM dbo.SplitString(@s1, ',', 1);   -- 1 / 2 / 3
SELECT * FROM dbo.SplitString(@s2, '###', 1); -- 1 / 2 / 3
SELECT * FROM dbo.SplitString(@s3, '###', 0); -- 1 / 2 / 3 / (空行保留)

效能與替代方案

進階：內聯表值函數 (iTVF) 版本

若追求極致效能，可改寫為 RETURNS TABLE ... AS RETURN SELECT ... 形式，配合 Tally Table 或 STRING_SPLIT 實現無迴圈集合運算，並能被優化器內聯展開。

使用提醒

• 分隔符含正則特殊字元（如 |、.）時，本函數以字面匹配，無需轉義。
• 大量資料建議在應用層分割後傳 TVP，減少資料庫 CPU 壓力。

一個靈活的分割函數，能讓許多「臨時拆分」需求變得優雅可控。