MSSQL text/ntext 類型無法用 REPLACE 解決方案：CAST 轉 varchar(max) 完美替換

問題現象

執行 UPDATE table SET column = REPLACE(column, 'old', 'new') 時，若 column 為 text 或 ntext 類型，報錯：

參數數據類型 text 對於 replace 函數的參數 1 無效

原因：text、ntext、image 屬於大對象 (LOB) 類型，不支持直接進行字符串函數操作 (REPLACE, SUBSTRING, CHARINDEX 等)。

終極解決方案：CAST 轉 varchar(max) / nvarchar(max)

UPDATE YourTable
SET YourColumn = REPLACE(CAST(YourColumn AS VARCHAR(MAX)), '舊文本', '新文本');

原理：VARCHAR(MAX) / NVARCHAR(MAX) / VARBINARY(MAX) 是 SQL Server 2005 引入的大值數據類型，最高支持 2^31-1 字節 (2GB)，完全替代舊 LOB 類型，且原生支持所有字符串函數。

為什麼不直接用 CAST(column AS VARCHAR(8000))？

大值類型 (MAX) 核心特性對比

Microsoft 官方建議：棄用 text/ntext/image

重要提示：請使用 varchar(max)、nvarchar(max) 和 varbinary(max) 數據類型，而不要使用 text、ntext 和 image 數據類型。

遷移建議：ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR(MAX); (元數據操作，極快，無數據移動)。

支持大值類型的字符串函數清單

COL_LENGTH, CHARINDEX, PATINDEX, LEN, DATALENGTH, SUBSTRING,
LEFT, RIGHT, REPLACE, STUFF, REVERSE, UPPER, LOWER,
LTRIM, RTRIM, ASCII, UNICODE, CHAR, NCHAR, QUOTENAME,
SPACE, STR, DIFFERENCE, SOUNDEX, CONCAT, CONCAT_WS (2017+)

進階用法：.WRITE 子句進行大文本部分更新 (高性能)

-- 僅替換第 1000 字符開始的 10 個字符，不重寫整個大值
UPDATE LargeTable
SET BigColumn.WRITE('NewText', 1000, 10)  -- @Offset 從 0 開始
WHERE Id = 123;

適用於大文本微量修改，避免全列重寫產生大量日志和頁分裂。

完整測試腳本

-- 1. 創建測試表
CREATE TABLE TestTextReplace (
    Id INT IDENTITY PRIMARY KEY,
    OldTextCol TEXT,
    NewMaxCol VARCHAR(MAX)
);

-- 2. 插入超長文本 (>8000)
DECLARE @long VARCHAR(MAX) = REPLICATE(CAST('ABCDEFGHIJ' AS VARCHAR(MAX)), 1000); -- 10000 字符
INSERT INTO TestTextReplace (OldTextCol) VALUES (@long);

-- 3. 直接 REPLACE 失敗
-- UPDATE TestTextReplace SET OldTextCol = REPLACE(OldTextCol, 'A', 'X'); -- 報錯

-- 4. 正確：CAST 為 VARCHAR(MAX) 後 REPLACE
UPDATE TestTextReplace 
SET NewMaxCol = REPLACE(CAST(OldTextCol AS VARCHAR(MAX)), 'A', 'X');

-- 5. 驗證
SELECT LEN(NewMaxCol), LEFT(NewMaxCol, 20) FROM TestTextReplace; -- 10000, 'XBCDEFGHIJ...'

-- 6. 遷移列類型 (生產環境建議)
-- ALTER TABLE TestTextReplace ALTER COLUMN OldTextCol VARCHAR(MAX);

常見誤區提醒