SQL Server 2005 自訂 Split 函數：以分隔符擷取指定位置子字串

SQL Server 內建無 SPLIT 函數，若需從逗號分隔字串（如 'aa,dd,cc,rr,fff'）取出第 N 個元素，可自行撰寫標量函數。本文提供一個相容 SQL Server 2005+ 的 dbo.core_split 函數，支援自訂分隔字元與零基底索引。

函數定義

CREATE FUNCTION [dbo].[core_split]
(
    @str     VARCHAR(8000),  -- 擴大長度支援長字串
    @split   CHAR(1),        -- 分隔字元
    @index   INT             -- 目標索引（0-based）
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @count INT = 0;
    DECLARE @result VARCHAR(8000) = '';
    DECLARE @pos INT;

    WHILE CHARINDEX(@split, @str) > 0
    BEGIN
        SET @pos = CHARINDEX(@split, @str);
        IF @count = @index
        BEGIN
            SET @result = SUBSTRING(@str, 1, @pos - 1);
            BREAK;
        END
        SET @str = STUFF(@str, 1, @pos, '');
        SET @count = @count + 1;
    END

    -- 處理最後一段（或無分隔符情況）
    IF CHARINDEX(@split, @str) = 0 AND @count = @index
        SET @result = @str;

    RETURN @result;
END;

使用範例

-- 取得第 1 個元素（索引 1 → 'ss'）
SELECT dbo.core_split('aa,ss,ee,rr,da,tt', ',', 1);  -- 結果：ss

-- 取得第 0 個元素（索引 0 → 'aa'）
SELECT dbo.core_split('aa,ss,ee,rr,da,tt', ',', 0);  -- 結果：aa

-- 索引超出範圍回傳空字串
SELECT dbo.core_split('a,b,c', ',', 5);  -- 結果：''

效能與替代方案提醒

• 此函數採逐字元迴圈，大量資料集效能較差。若頻繁在大表上使用，建議改用：
• SQL Server 2016+：內建 STRING_SPLIT（但不保證順序、無索引）。
• SQL Server 2022 / Azure SQL：STRING_SPLIT 新增 ordinal 輸出，可直接 WHERE ordinal = @index。
• CLR 函數：以 .NET String.Split 實作，效能大幅優於 T-SQL 迴圈。
• 數值表：預先建立遞增數值表，搭配 CROSS APPLY 與 CHARINDEX/SUBSTRING 集合式處理。

改良版（支援多字元分隔符、長度優化）

CREATE FUNCTION [dbo].[SplitString]
(
    @str     NVARCHAR(MAX),
    @split   NVARCHAR(10),
    @index   INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML = CAST('' AS XML);
    RETURN @xml.value('(/r[position()=sql:variable("@index")+1])[1]', 'NVARCHAR(MAX)');
END;

XML 寫法簡潔且效能較迴圈佳，但極長字串（> 數 MB）可能記憶體壓力大，視情境選擇。

掌握字串分割技巧，能靈活處理傳入參數、動態 IN 清單、ETL 資料剖析等常見情境。