SQL Server 處理 NTEXT/TEXT 大欄位替換的兩種實用方法

背景

NTEXT、TEXT、IMAGE 為舊版大型資料類型（SQL Server 2005 起建議改用 NVARCHAR(MAX)/VARCHAR(MAX)/VARBINARY(MAX)）。這些類型不支援 REPLACE() 等字串函數，直接操作會報錯。

方法一：轉型為 VARCHAR(MAX) 再 REPLACE（適用 ≤ 8000 字元）

UPDATE MyTable
SET LargeColumn = REPLACE(CAST(LargeColumn AS VARCHAR(MAX)), 'old_text', 'new_text')
WHERE LargeColumn LIKE '%old_text%';

• 優點：語法簡單，單陳述式完成。
• 缺點：VARCHAR(MAX) 仍受 2 GB 限制，但中介轉換可能產生大量記錄檔；超過 8000 字元時隱含轉換可能截斷（視相容性層級）。

方法二：使用 UPDATETEXT 逐段替換（標準做法，支援任意長度）

-- 1. 建立測試環境
CREATE TABLE #Test (BigCol TEXT);
INSERT INTO #Test SELECT 'abc123abc123,asd';

-- 2. 宣告變數
DECLARE @SearchStr VARCHAR(8000) = '123',
        @ReplaceStr VARCHAR(8000) = '000',
        @Ptr VARBINARY(16),
        @Pos INT,
        @Len INT;

-- 3. 取得文字指標與搜尋長度
SELECT @Ptr = TEXTPTR(BigCol),
       @Len = LEN(@SearchStr),
       @Pos = CHARINDEX(@SearchStr, BigCol) - 1
FROM #Test;

-- 4. 迴圈替換所有出現位置
WHILE @Pos > 0
BEGIN
    UPDATETEXT #Test.BigCol @Ptr @Pos @Len @ReplaceStr;
    SELECT @Pos = CHARINDEX(@SearchStr, BigCol) - 1 FROM #Test;
END

-- 5. 驗證結果
SELECT * FROM #Test;  -- 輸出: abc000abc000,asd

-- 6. 清理
DROP TABLE #Test;

關鍵函數說明

最佳實務建議

1. 優先遷移型別：執行 ALTER TABLE ... ALTER COLUMN BigCol NVARCHAR(MAX)，後續即可直接用 REPLACE()。
2. 若必須維持舊型別，封裝方法二為預存程序 sp_ReplaceText @Table, @Column, @Search, @Replace 重複使用。
3. 大量更新時，考慮分批次執行並搭配 CHECKPOINT 控制記錄檔增長。
4. SQL Server 2016+ 可使用 STRING_SPLIT + STRING_AGG 實現集合式替換邏輯（需相容性層級 130+）。