SQL Server 2005 字串函數實戰：解析逗號分隔字串並批次寫入資料表

需求場景

表 Code 欄位 String 儲存格式如 'dsddddd,2222222,222221,3'，最後一位為標記 (BiaoJi)，前面逗號分隔的值對應 Value，需拆解寫入表 Record(BiaoJi, Value, Time)。

存儲過程實作

USE Demo;
GO

DROP PROC IF EXISTS proc_split_Code;
GO

CREATE PROC proc_split_Code
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Total INT, @Idx INT = 1;
    SELECT @Total = COUNT(*) FROM Code;

    WHILE @Idx <= @Total
    BEGIN
        DECLARE @BiaoJi INT, @Str NVARCHAR(1000), @Pos INT, @Start INT = 0, @Val NVARCHAR(100);

        -- 取第 @Idx 筆資料，利用 ROW_NUMBER 定序
        SELECT @Str = REVERSE(String)
        FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY String) AS rn, *
            FROM Code
        ) t
        WHERE rn = @Idx;

        -- 取最後一位逗號前的字元作為標記 (原字串最後一位)
        SET @Pos = CHARINDEX(N',', @Str, @Start);
        SET @BiaoJi = CAST(SUBSTRING(@Str, @Start, @Pos) AS INT);

        -- 還原字串順序，逐段解析值
        SET @Str = REVERSE(@Str);
        SET @Pos = CHARINDEX(N',', @Str, @Start);

        WHILE @Pos > 0
        BEGIN
            SET @Val = SUBSTRING(@Str, @Start, @Pos - @Start);
            INSERT INTO Record (BiaoJi, Value, Time) VALUES (@BiaoJi, @Val, GETDATE());
            SET @Start = @Pos + 1;
            SET @Pos = CHARINDEX(N',', @Str, @Start);
        END

        -- 處理最後一段 (若無逗號結尾)
        IF @Start < LEN(@Str)
        BEGIN
            SET @Val = SUBSTRING(@Str, @Start, LEN(@Str) - @Start + 1);
            INSERT INTO Record (BiaoJi, Value, Time) VALUES (@BiaoJi, @Val, GETDATE());
        END

        SET @Idx += 1;
    END
END;
GO

EXEC proc_split_Code;

關鍵函數說明

• REVERSE()：反轉字串，方便從右向左找最後一個逗號 (標記位)。
• CHARINDEX(sub, str, start)：尋找子字串位置，支援起始位置。
• SUBSTRING(str, start, len)：擷取子字串。
• ROW_NUMBER() OVER (ORDER BY ...)：為游標迴圈提供確定順序。

效能與改進建議

• 單筆逐行 INSERT 效能較差，大量資料建議改用 STRING_SPLIT (SQL 2016+) 或 XML/JSON 拆分後批次 INSERT。
• 若格式固定，可考慮 PARSENAME(REPLACE(String, ',', '.'), 4) 等技巧快速取第 N 段。
• 生產環境務必加入 TRY...CATCH、交易控制與輸入驗證。