SQL Server 實現 SPLIT 字串分割函數三種經典寫法總結

方法一：基本 WHILE 迴圈實現（支援單字元分隔符）

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', ',')
-- 結果：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
    
    IF CHARINDEX(@split_str, @long_str_Tmp) = 0
        INSERT INTO @tmp SELECT @long_str_Tmp
    ELSE
    BEGIN
        WHILE CHARINDEX(@split_str, @long_str_Tmp) > 0
        BEGIN
            SET @short_str = SUBSTRING(@long_str_Tmp, 1, CHARINDEX(@split_str, @long_str_Tmp)-1)
            DECLARE @long_str_Tmp_LEN INT, @split_str_Position_END INT
            SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)
            SET @split_str_Position_END = LEN(@short_str) + @split_str_length
            SET @long_str_Tmp = REVERSE(SUBSTRING(REVERSE(@long_str_Tmp), 1, @long_str_Tmp_LEN-@split_str_Position_END))
            IF @short_str <> '' 
                INSERT INTO @tmp SELECT @short_str
        END
    END
    RETURN
END

-- 使用範例
SELECT * FROM DBO.F_SQLSERVER_SPLIT('1203401230105045', '0')
SELECT * FROM DBO.F_SQLSERVER_SPLIT('abc1234a12348991234', '1234')

方法三：SQL 2000/2005 相容寫法

SQL 2000 版本

CREATE FUNCTION [dbo].[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 INTO @array(onestr) VALUES(@v_code)
    END
    RETURN
END

SQL 2005+ 版本（利用 XML 特性，效能最佳）

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

效能建議

• 小資料量：方法一簡單易懂
• 多字元分隔符：方法二功能完整
• 大資料量/高頻呼叫：SQL 2005+ XML 寫法效能最優