MSSQL 字串分段更新：以指定分隔符替換第 N 段內容

需求：將以逗號分隔的字串 'a,a,a,a,a' 中第 3 段替換為 'test'，結果為 'a,a,a,test,a'。本文提供純 T-SQL 迴圈演算法，無需 CLR 或自訂函數即可完成分段定位與替換。

核心邏輯

1. 宣告變數：原字串、分隔符、分隔符長度、目標段位、替換值、迴游標位置。
2. 用 CHARINDEX 迴圈定位第 @pos 個分隔符的起始/結束位置。
3. 根據位置關係用 STUFF 執行替換/插入/附加。

完整可執行腳本

DECLARE @s VARCHAR(20) = 'a,a,a,a,a';      -- 原字串
DECLARE @split VARCHAR(10) = ',';           -- 分隔符
DECLARE @splitlen INT = LEN(@split + 'a') - 2; -- 分隔符實際長度 (此處為 1)
DECLARE @pos INT = 3;                       -- 目標段位 (第 3 段)
DECLARE @value VARCHAR(10) = 'test';        -- 替換值
DECLARE @I INT = 1, @J INT;                 -- 迴圈游標

-- 找到第一個分隔符位置
SELECT @J = CHARINDEX(@split, @s + @split);

-- 迴圈定位到第 @pos 段的起始位置 @I
WHILE @pos > 0 AND @I <= @J
BEGIN
    SELECT @pos = @pos - 1,
           @I = @J + @splitlen + 1,
           @J = CHARINDEX(@split, @s + @split, @I);
END

-- 根據位置關係執行替換
SELECT @s = CASE
    WHEN @I < @J THEN STUFF(@s, @I, @J - @I, @value)        -- 中間段：替換
    WHEN @J > LEN(@s) THEN @s + @value                      -- 尾段超出：附加
    WHEN @I = @J THEN STUFF(@s, @I, 0, @value)              -- 空段：插入
    ELSE @s END;                                            -- 其他：保持原樣

SELECT @s AS Result;  -- 輸出: a,a,a,test,a

關鍵函數說明

• CHARINDEX(sub, str, start)：從 start 位置尋找子字串，找不到傳回 0。
• STUFF(str, start, length, new_str)：刪除 start 起 length 字元並插入 new_str。
• @s + @split：尾部附加分隔符確保最後一段也能被 CHARINDEX 找到。

擴充建議

• 封裝為標量函數 dbo.fn_ReplaceSegment(@str, @delim, @segment, @newVal) 供重複呼叫。
• 大量資料更新時，建議改用 CROSS APPLY 搭配 STRING_SPLIT (2016+) 或 XML 分割法，避免逐列迴圈效能問題。
• 若分隔符為多字元 (如 '||')，@splitlen 計算邏輯仍適用。

掌握此技巧，即可在純 T-SQL 中靈活處理分隔字串的精準分段修改。