SQL Server 自訂字串分割函數 Split() 完整實作：支援 NTEXT/TEXT 大型資料與高效能批量操作

為什麼需要自訂 Split？

SQL Server 內建無 STRING_SPLIT（2016 才引入），早期版本須自行實作。批量操作（如一次傳入數千 ID 進行 IN 查詢或批次更新）若逐筆呼叫，網路往返開銷極大；透過單一字串參數 + Split 函數轉為資料表值，可大幅降低 Round-trip。

資料型別選擇策略

關鍵點： 區域變數無法宣告 TEXT/NTEXT，但可直接將 SUBSTRING 結果 INSERT 進表值變數，避免中間賦值。

高效能 Split 函數完整程式碼

CREATE FUNCTION dbo.Split
(
    @SplitString TEXT,              -- 或 NTEXT
    @Separator VARCHAR(2) = ','     -- 或 NVARCHAR(2)
)
RETURNS @SplitStringsTable TABLE
(
    [id] INT IDENTITY(1,1),
    [value] VARCHAR(8000)           -- 或 NVARCHAR(4000)
)
AS
BEGIN
    DECLARE @CurrentIndex INT, @NextIndex INT, @ReturnText VARCHAR(8000);
    SELECT @CurrentIndex = 1;

    WHILE (@CurrentIndex <= DATALENGTH(@SplitString))  -- NTEXT 時用 DATALENGTH/2
    BEGIN
        SELECT @NextIndex = CHARINDEX(@Separator, @SplitString, @CurrentIndex);
        IF (@NextIndex = 0 OR @NextIndex IS NULL)
            SELECT @NextIndex = DATALENGTH(@SplitString) + 1;

        SELECT @ReturnText = SUBSTRING(@SplitString, @CurrentIndex, @NextIndex - @CurrentIndex);
        INSERT INTO @SplitStringsTable([value]) VALUES (@ReturnText);
        SELECT @CurrentIndex = @NextIndex + 1;
    END
    RETURN;
END

使用範例

-- 批量刪除
DELETE FROM Orders
WHERE OrderID IN (SELECT value FROM dbo.Split('10248,10249,10250', ','));

-- 批量更新搭配 JOIN
UPDATE p
SET p.Status = 'Shipped'
FROM Production.Product p
JOIN dbo.Split('707,708,709', ',') s ON p.ProductID = CAST(s.value AS INT);

效能優化建議

• SQL Server 2016+ 直接使用內建 STRING_SPLIT（但無順序保證，2022 新增 ordinal 欄位）。
• 大量分割時，考慮 CLR 實作或 XML 方案（CAST('' + REPLACE(@str, ',', '') + '' AS XML)）。
• 避免在 WHILE 迴圈內頻繁呼叫 CHARINDEX，可預先計算分隔符位置陣列（CLR 優勢）。