SQL Server 中 text、ntext 類型**不支援直接 REPLACE**,需轉換後處理。本文提供三種場景方案:短文本轉換替換、超長文本游標替換、結構修改替換列類型。
轉換為支援 REPLACE 的類型後更新:
-- text 類型
UPDATE 表名
SET text欄位 = REPLACE(CONVERT(varchar(8000), text欄位), '舊字串', '新字串');
-- ntext 類型
UPDATE 表名
SET ntext欄位 = REPLACE(CONVERT(nvarchar(4000), ntext欄位), N'舊字串', N'新字串');
利用 TEXTPTR + UPDATETEXT 逐段定位替換,避免截斷:
DECLARE @pos INT, @len INT, @str NVARCHAR(4000), @des NVARCHAR(4000), @count INT;
SET @des = N''; -- 目標標記
SET @len = LEN(@des);
SET @str = N''; -- 替換內容
SET @count = 0;
WHILE 1=1
BEGIN
SELECT @pos = PATINDEX('%' + @des + '%', propxmldata) - 1
FROM 表名 WHERE 條件;
IF @pos >= 0
BEGIN
DECLARE @ptrval BINARY(16);
SELECT @ptrval = TEXTPTR(字段名) FROM 表名 WHERE 條件;
UPDATETEXT 表名.字段名 @ptrval @pos @len @str;
SET @count = @count + 1;
END
ELSE BREAK;
END
SELECT @count AS 替換次數;
新版本建議直接改用大值類型,但 ALTER COLUMN 不支援 text/ntext/image/timestamp。變通方案:
ALTER TABLE tbl ADD newcol NVARCHAR(MAX) 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
後續必須刷新視圖,否則視圖元數據過期報錯:
-- 刷新單個視圖
EXEC sp_refreshview 'ViewName';
-- 批量刷新所有視圖
DECLARE @vName SYSNAME;
DECLARE cur CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'V';
OPEN cur;
FETCH NEXT FROM cur INTO @vName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @vName;
PRINT 'Refreshed: ' + @vName;
FETCH NEXT FROM cur INTO @vName;
END
CLOSE cur; DEALLOCATE cur;
varchar(max)/nvarchar(max),原生支援 REPLACE