萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server text/ntext 大字段內容替換完整方案(含超長文本處理)

SQL Server 中 text、ntext 類型**不支援直接 REPLACE**,需轉換後處理。本文提供三種場景方案:短文本轉換替換、超長文本游標替換、結構修改替換列類型。

場景一:文本長度 ≤ 8000 (varchar) / 4000 (nvarchar)

轉換為支援 REPLACE 的類型後更新:

-- text 類型
UPDATE 表名
SET text欄位 = REPLACE(CONVERT(varchar(8000), text欄位), '舊字串', '新字串');

-- ntext 類型
UPDATE 表名
SET ntext欄位 = REPLACE(CONVERT(nvarchar(4000), ntext欄位), N'舊字串', N'新字串');

場景二:超長文本(>8000/4000)分塊替換

利用 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 替換次數;

場景三:修改列類型為 varchar(max)/nvarchar(max) (SQL 2005+)

新版本建議直接改用大值類型,但 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;

版本建議

  • SQL Server 2005/2008:用場景一或二
  • SQL Server 2012+:優先場景三改為 varchar(max)/nvarchar(max),原生支援 REPLACE
copyright © 萬盛學電腦網 all rights reserved