SQL Server 字串批次取代：REPLACE 函數搭配 CAST/CONVERT 處理 TEXT/NTEXT/大型值型別

型別相容性矩陣

核心語法模式

現代型別 (MAX) 直接取代

UPDATE dbo.Articles
SET Content = REPLACE(Content, 'old.domain.com', 'new.domain.com')
WHERE Content LIKE '%old.domain.com%'

舊型別 TEXT/NTEXT 需轉型 (≤ 8000/4000 字元)

-- VARCHAR(8000) 上限
UPDATE dbo.OldTable
SET TextCol = REPLACE(CAST(TextCol AS VARCHAR(8000)), '舊字串', '新字串')

-- NVARCHAR(4000) 上限
UPDATE dbo.OldTable
SET NTextCol = REPLACE(CAST(NTextCol AS NVARCHAR(4000)), N'舊字串', N'新字串')

大型值型別 VARCHAR(MAX)/NVARCHAR(MAX) 完整支援

-- 無長度限制，直接操作
UPDATE dbo.LargeContent
SET Body = REPLACE(Body, 'https://', 'https://')
WHERE Body LIKE '%https://%'

進階場景與技巧

多重取代鏈式

UPDATE dbo.Templates
SET Template = REPLACE(REPLACE(REPLACE(Template, 
    '{{UserName}}', @UserName), '{{Date}}', CONVERT(VARCHAR, GETDATE(), 111)), '{{ID}}', @ID)

條件式取代 (CASE + REPLACE)

UPDATE dbo.Products
SET Description = CASE 
    WHEN Description LIKE '%舊品牌%' THEN REPLACE(Description, '舊品牌', '新品牌')
    WHEN Description LIKE '%廢止%' THEN REPLACE(Description, '廢止', '停產')
    ELSE Description END

跨表同步取代

UPDATE t
SET t.Content = REPLACE(t.Content, s.OldValue, s.NewValue)
FROM dbo.TargetTable t
JOIN dbo.ReplaceMap s ON t.Content LIKE '%' + s.OldValue + '%'
WHERE s.IsActive = 1

效能優化關鍵

1. WHERE 子句先過濾：WHERE Col LIKE '%search%' 避免全表掃描無異動列
2. 索引支援：LIKE 'prefix%' 可用索引；LIKE '%middle%' 需全文檢索
3. 分批處理大量資料：

   DECLARE @BatchSize INT = 5000, @RowCount INT = 1
   WHILE @RowCount > 0
   BEGIN
       UPDATE TOP (@BatchSize) dbo.HugeTable
       SET Col = REPLACE(Col, 'old', 'new')
       WHERE Col LIKE '%old%'
       SET @RowCount = @@ROWCOUNT
       WAITFOR DELAY '00:00:01'  -- 降低鎖爭用
   END

4. 最小日誌模式：簡單/大量記錄復原模式 + TABLOCK 提示 (需測試)

TEXT/NTEXT 超過 8000 字元處理

舊型別單次 CAST 會截斷。解法：

1. 應用層分塊讀取、取代、寫回 (C#/Java/Python)
2. 遷移至 VARCHAR(MAX)/NVARCHAR(MAX)：

   ALTER TABLE dbo.OldTable ALTER COLUMN TextCol VARCHAR(MAX)  -- 線上操作 (Enterprise)
   -- 或
   SELECT CAST(TextCol AS VARCHAR(MAX)) INTO #Tmp FROM dbo.OldTable
   -- 重建表

3. UPDATETEXT (已淘汰)：UPDATETEXT Table.TextCol @ptr @offset @delete_len @insert_data，極度複雜、不建議

實務範例：網域遷移全站連結更新

-- 1. 確認影響範圍
SELECT COUNT(*) FROM dbo.Pages WHERE Content LIKE '%old-site.com%'

-- 2. 分批更新 (假設 100 萬列)
DECLARE @Batch INT = 10000, @Done INT = 1
WHILE @Done > 0
BEGIN
    UPDATE TOP (@Batch) dbo.Pages
    SET Content = REPLACE(Content, 'https://old-site.com', 'https://new-site.com')
    WHERE Content LIKE '%https://old-site.com%'
    SET @Done = @@ROWCOUNT
    PRINT CAST(@Done AS VARCHAR) + ' rows updated at ' + CONVERT(VARCHAR, GETDATE(), 114)
    WAITFOR DELAY '00:00:02'
END

-- 3. 驗證
SELECT COUNT(*) FROM dbo.Pages WHERE Content LIKE '%old-site.com%'

版本與型別建議

• SQL Server 2005+：全面支援 VARCHAR(MAX)/NVARCHAR(MAX)
• 新開發嚴禁使用 TEXT/NTEXT (已淘汰、功能受限、未來移除)
• 遷移腳本納入資料庫版本控管，上線前驗證