SQL 實現 SPLIT 字串分割函數多種方案總結

SQL 實現 SPLIT 字串分割函數多種方案總結

SQL Server 無內建 SPLIT 函數，常需自行實現。本文整理三種主流方案。

方案一：迴圈插入暫存表 (通用、相容 SQL 2000+)

CREATE FUNCTION f_split(@SourceSql VARCHAR(8000), @StrSeprate VARCHAR(10))
RETURNS @temp TABLE (a VARCHAR(100))
AS BEGIN
    DECLARE @i INT
    SET @SourceSql = RTRIM(LTRIM(@SourceSql))
    SET @i = CHARINDEX(@StrSeprate, @SourceSql)
    WHILE @i >= 1 BEGIN
        INSERT @temp VALUES (LEFT(@SourceSql, @i-1))
        SET @SourceSql = SUBSTRING(@SourceSql, @i+1, LEN(@SourceSql)-@i)
        SET @i = CHARINDEX(@StrSeprate, @SourceSql)
    END
    IF @SourceSql <> '' INSERT @temp VALUES (@SourceSql)
    RETURN
END
-- 測試
SELECT * FROM dbo.f_split('1,2,3,4', ',')  -- 返回 4 行：1,2,3,4

方案二：支援多字元分隔符、反轉技巧處理尾部分隔符

CREATE FUNCTION F_SQLSERVER_SPLIT(@Long_str VARCHAR(8000), @split_str VARCHAR(100))
RETURNS @tmp TABLE (ID INT IDENTITY PRIMARY KEY, short_str VARCHAR(8000))
AS BEGIN
    DECLARE @long_str_Tmp VARCHAR(8000), @short_str VARCHAR(8000), @split_str_length INT
    SET @split_str_length = LEN(@split_str)
    -- 處理開頭分隔符
    IF CHARINDEX(@split_str, @Long_str) = 1
        SET @long_str_Tmp = SUBSTRING(@Long_str, @split_str_length+1, LEN(@Long_str)-@split_str_length)
    ELSE
        SET @long_str_Tmp = @Long_str
    -- 強制補尾部分隔符便於迴圈
    IF CHARINDEX(REVERSE(@split_str), REVERSE(@long_str_Tmp)) > 1
        SET @long_str_Tmp = @long_str_Tmp + @split_str
    -- 迴圈分割
    WHILE CHARINDEX(@split_str, @long_str_Tmp) > 0 BEGIN
        SET @short_str = SUBSTRING(@long_str_Tmp, 1, CHARINDEX(@split_str, @long_str_Tmp)-1)
        -- 移除已處理部分
        SET @long_str_Tmp = REVERSE(SUBSTRING(REVERSE(@long_str_Tmp), 1, LEN(@long_str_Tmp) - LEN(@short_str) - @split_str_length))
        IF @short_str <> '' INSERT @tmp SELECT @short_str
    END
    RETURN
END

方案三：SQL 2005+ 利用 XML 原生分割 (效能最佳、程式碼最簡)

-- SQL 2000 版 (迴圈法)
CREATE FUNCTION splitstring_array(@string NVARCHAR(4000), @split CHAR(1))
RETURNS @array TABLE (oneStr NVARCHAR(100))
AS BEGIN
    DECLARE @v_code VARCHAR(60)
    WHILE LEN(@string) > 0 BEGIN
        IF CHARINDEX(@split, @string, 1) != 0 BEGIN
            SET @v_code = SUBSTRING(@string, 1, CHARINDEX(@split, @string, 1)-1)
            SET @string = SUBSTRING(@string, CHARINDEX(@split, @string, 1)+1, LEN(@string))
        END ELSE BEGIN
            SET @v_code = @string
            SET @string = ''
        END
        INSERT @array VALUES (@v_code)
    END
    RETURN
END

-- SQL 2005+ 版 (XML 法)
CREATE FUNCTION func_splitid(@str VARCHAR(MAX), @split VARCHAR(10))
RETURNS @t TABLE (c1 NVARCHAR(100))
AS BEGIN
    DECLARE @x XML
    SET @x = CONVERT(XML, '')
    INSERT @t SELECT x.item.value('@id[1]', 'NVARCHAR(100)') FROM @x.nodes('//items/item') AS x(item)
    RETURN
END

選型建議

• SQL 2000/相容性要求高 → 方案一或方案三 SQL 2000 版
• 需支援多字元分隔符 → 方案二
• SQL 2005+ 追求效能與簡潔 → 方案三 XML 版 (強烈推薦)

根據版本與需求選擇適合的分割函數實現，XML 方案在大數據量分割場景效能優勢明顯。