SQL Server 2000 中 TEXT/NTEXT 大物件欄位不支援 REPLACE() 函數，需使用 UPDATETEXT 配合 TEXTPTR()、PATINDEX() 進行位元組層級的部分更新。本文提供的預存程序**僅支援 ASCII 字元**（不支援中文雙位元組字元），適用於舊版系統維護場景。
/*
Author: amjn
Date: 2003-03-28
Version: 1.0
Function: 替換 TEXT/NTEXT 欄位中的指定字串（不支援中文）
*/
CREATE PROCEDURE usp_ReplaceTextColumn
@TableName SYSNAME, -- 目標資料表
@ColumnName SYSNAME, -- 目標 TEXT/NTEXT 欄位
@PKColumn SYSNAME, -- 主鍵欄位名稱
@SearchStr VARCHAR(8000),-- 尋找字串
@ReplaceStr VARCHAR(8000) -- 取代字串
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PtrVal VARBINARY(16),
@PKVal INT,
@Pos INT,
@SearchLen INT,
@SQL NVARCHAR(4000),
@ParamDef NVARCHAR(500);
SET @SearchLen = DATALENGTH(@SearchStr);
IF @SearchLen = 0 RETURN; -- 空搜尋字串直接返回
-- 宣告游標遍歷目標資料列
DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT TEXTPTR(@ColumnName), @PKColumn
FROM @TableName
WHERE PATINDEX('%' + @SearchStr + '%', @ColumnName) > 0;
OPEN cur;
FETCH NEXT FROM cur INTO @PtrVal, @PKVal;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 取得第一次出現位置（1-based，UPDATETEXT 為 0-based）
SELECT @Pos = PATINDEX('%' + @SearchStr + '%', @ColumnName) - 1
FROM @TableName
WHERE @PKColumn = @PKVal;
WHILE @Pos >= 0
BEGIN
-- 動態建構 UPDATETEXT 陳述式
SET @SQL = N'UPDATETEXT ' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) +
N' @Ptr @Pos @Len @Repl';
SET @ParamDef = N'@Ptr VARBINARY(16), @Pos INT, @Len INT, @Repl VARCHAR(8000)';
EXEC sp_executesql @SQL, @ParamDef,
@Ptr = @PtrVal, @Pos = @Pos, @Len = @SearchLen, @Repl = @ReplaceStr;
-- 尋找下一個出現位置
SELECT @Pos = PATINDEX('%' + @SearchStr + '%', @ColumnName) - 1
FROM @TableName
WHERE @PKColumn = @PKVal;
END
FETCH NEXT FROM cur INTO @PtrVal, @PKVal;
END
CLOSE cur;
DEALLOCATE cur;
END
GO-- 將 USA201 表 Name 欄位中所有 'ILOVEHJL' 替換為 'i love hjl'
EXEC usp_ReplaceTextColumn
@TableName = 'USA201',
@ColumnName = 'Name',
@PKColumn = 'amjnId',
@SearchStr = 'ILOVEHJL',
@ReplaceStr = 'i love hjl';| 技術點 | 說明 |
|---|---|
TEXTPTR() | 取得 TEXT/NTEXT 欄位的 16 位元組指標，供 UPDATETEXT 定位。 |
PATINDEX() | 回傳字串首次出現的**字元位置（1-based）**，UPDATETEXT 需轉為**位元組偏移（0-based）**，故 -1。 |
UPDATETEXT | 語法：UPDATETEXT table.column @ptr @offset @deletelen @insertstr。@offset = 位元組偏移，@deletelen = 刪除位元組數，@insertstr = 插入字串。 |
游標 FAST_FORWARD READ_ONLY | 唯讀、順向、最佳化效能，避免鎖定升級。 |
動態 SQL + sp_executesql | 參數化執行，防止 SQL 注入，重複使用執行計畫。 |
PATINDEX 以字元為單位，UPDATETEXT 以位元組為單位。中文字在 TEXT 中佔 2 位元組（DBCS），位置計算會錯位。NTEXT 則為 2 位元組固定（UCS-2），同樣不適用。Chinese_PRC_CI_AS 不區分大小寫。PATINDEX + 動態 SQL，大量資料極慢。百萬列級建議改用 BCP OUT → 文字處理 → BCP IN 離線處理。UPDATETEXT 產生記錄，大量更新需設定 SIMPLE 復原模式或分批 CHECKPOINT。-- SQL 2005+ 直接支援 MAX 類型的 REPLACE
UPDATE LargeTable
SET LargeColumn = REPLACE(CAST(LargeColumn AS VARCHAR(MAX)), 'old', 'new')
WHERE LargeColumn LIKE '%old%';
-- 或使用 .NET CLR 實作高效能串流替換此預存程序為 SQL 2000 時代處理 TEXT/NTEXT 部分更新的經典方案，**僅適用純 ASCII 環境**。現代系統應遷移至 VARCHAR(MAX)/NVARCHAR(MAX) 並使用原生 REPLACE()。若必須維護舊系統，請充分測試中文資料情況，並考慮離線批次處理替代線上游標更新。
