SQL Server 資料內容批次替換完整方案 (含 ntext 處理)

適用資料類型

除 ntext/text/image 舊型別外，所有字串型別 (char, varchar, nchar, nvarchar) 均可直接使用 REPLACE 函數。

標準替換語法

UPDATE [資料表名] SET [欄位名] = REPLACE([欄位名], '舊字串', '新字串');
-- 範例：替換文章標題中的舊路徑
UPDATE [iwms_news] SET [title] = REPLACE([title], '200901/14', '200901/15');

ntext/text 型別處理方案

方案一：型別轉換法 (適用單欄位長度 < 4000 字元)

UPDATE [資料表名]
SET [欄位名] = REPLACE(CONVERT(VARCHAR(4000), [欄位名]), '舊字串', '新字串');
-- 範例：替換文章內容
UPDATE [iwms_news] SET [content] = REPLACE(CONVERT(VARCHAR(4000), [content]), '舊字串', '新字串');

方案二：UPDATETEXT + 游標法 (適用超長欄位)

DECLARE @ptr VARBINARY(16), @id INT, @pos INT, @len INT;
SET @len = DATALENGTH('舊字串');

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT TEXTPTR([欄位名]), [主鍵欄位] FROM [資料表名] FOR READ ONLY;

OPEN cur;
FETCH NEXT FROM cur INTO @ptr, @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @pos = PATINDEX('%舊字串%', [欄位名]) FROM [資料表名] WHERE [主鍵欄位] = @id;
    WHILE @pos > 0
    BEGIN
        SET @pos = @pos - 1;  -- UPDATETEXT 位移為 0-based
        UPDATETEXT [資料表名].[欄位名] @ptr @pos @len '新字串';
        SELECT @pos = PATINDEX('%舊字串%', [欄位名]) FROM [資料表名] WHERE [主鍵欄位] = @id;
    END
    FETCH NEXT FROM cur INTO @ptr, @id;
END
CLOSE cur; DEALLOCATE cur;

實務注意事項

1. 備份優先：大量更新前務必完整備份資料庫或建立快照。
2. 分頁內容：若系統將長文分頁儲存 (如 iwms_pages)，需同步更新所有相關表。
3. 交易控制：建議包裝在明確交易中，驗證影響列數後再提交。
4. 效能考量：大量資料建議分批處理 (如每批 1 萬列)，避免長時間鎖定與記錄檔暴增。
5. 執行環境：游標方案僅能在 SSMS/查詢分析器中執行，不可置於應用程式參數化命令中。

現代化替代建議

SQL Server 2005+ 建議將 ntext/text/image 遷移至 NVARCHAR(MAX)/VARCHAR(MAX)/VARBINARY(MAX)，即可直接使用 REPLACE、STRING_AGG 等現代函數，並享有更佳效能與壓縮支援。