SQL Server text/ntext 大字段內容替換全方案：REPLACE 限制突破與 UPDATETEXT 逐段寫入

痛點

text/ntext (已棄用，建議遷移至 varchar(max)/nvarchar(max)) 不支援直接 REPLACE，且長度超過 8000/4000 字符時無法轉換為 varchar/nvarchar 再替換。

方案一：小字段轉換法 (≤ 8000/4000 字符)

-- text → varchar(8000)
UPDATE tbl SET txt_col = REPLACE(CONVERT(varchar(8000), txt_col), 'old', 'new');

-- ntext → nvarchar(4000)
UPDATE tbl SET ntxt_col = REPLACE(CONVERT(nvarchar(4000), ntxt_col), 'old', 'new');

方案二：大字段 UPDATETEXT 逐段替換 (> 8000/4000)

DECLARE @pos INT, @len INT, @str NVARCHAR(4000), @des NVARCHAR(4000), @count INT;
SET @des = '';          -- 目標標記
SET @len = LEN(@des);
SET @str = '';           -- 替換內容
SET @count = 0;

WHILE 1=1
BEGIN
    -- 定位下一個匹配位置
    SELECT @pos = PATINDEX('%' + @des + '%', propxmldata) - 1
    FROM tbl WHERE 條件;

    IF @pos >= 0
    BEGIN
        DECLARE @ptrval BINARY(16);
        SELECT @ptrval = TEXTPTR(字段名) FROM tbl WHERE 條件;
        UPDATETEXT tbl.字段名 @ptrval @pos @len @str;
        SET @count = @count + 1;
    END
    ELSE BREAK;
END
SELECT @count AS 替換次數;

關鍵點：TEXTPTR 獲取文本指針，UPDATETEXT 定位偏移量覆寫，循環直至無匹配。

方案三：列重建法 (修改列屬性/允許 NULL)

ALTER COLUMN 不支援 text/ntext/image/timestamp。繞過方案：

ALTER TABLE tbl ADD newcol NTEXT NULL;
GO
UPDATE tbl SET newcol = col;
GO
EXEC sp_rename 'tbl.col', 'oldcol', 'COLUMN';
GO
EXEC sp_rename 'tbl.newcol', 'col', 'COLUMN';
GO
ALTER TABLE tbl DROP COLUMN oldcol;
GO

同步刷新依賴視圖：

DECLARE @vName SYSNAME;
DECLARE cur CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'V' ORDER BY crdate;
OPEN cur;
FETCH NEXT FROM cur INTO @vName;
WHILE @@FETCH_STATUS <> -1
BEGIN
    EXEC sp_refreshview @vName;
    FETCH NEXT FROM cur INTO @vName;
END
CLOSE cur; DEALLOCATE cur;

遷移建議

SQL Server 2005+ 應盡快將 text/ntext/image 遷移至 varchar(max)/nvarchar(max)/varbinary(max)，原生支援 REPLACE、索引、壓縮，維護成本大幅降低。