舊方案：VBScript.RegExp (sp_OA*)

CREATE FUNCTION dbo.regexReplace (
    @source NVARCHAR(MAX),
    @regexp VARCHAR(1000),
    @replace VARCHAR(1000),
    @globalReplace BIT = 1,
    @ignoreCase BIT = 0
) RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @hr INT, @objRegExp INT, @result VARCHAR(5000);
    EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT;
    -- ... 設定 Pattern、Global、IgnoreCase ...
    EXEC @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace;
    EXEC @hr = sp_OADestroy @objRegExp;
    RETURN @result;
END

**缺點**：需啟用 Ole Automation Procedures (安全風險)、效能差、不支援 64 位元、字串長度限制 8000 字元。

現代方案：CLR 正則函數 (推薦)

1. C# 專案代碼

using System;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlString RegexReplace(SqlString input, SqlString pattern, SqlString replacement, 
        SqlBoolean global, SqlBoolean ignoreCase)
    {
        if (input.IsNull) return SqlString.Null;
        var options = RegexOptions.None;
        if (ignoreCase.Value) options |= RegexOptions.IgnoreCase;
        if (!global.Value) options |= RegexOptions.RightToLeft; // 單次替換技巧
        var result = Regex.Replace(input.Value, pattern.Value, replacement.Value, options);
        return new SqlString(result);
    }
}

2. 部署步驟

1. 編譯為 DLL (目標 .NET Framework 4.x)
2. 啟用 CLR：EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
3. 建立組件：
   CREATE ASSEMBLY RegexUtils FROM 'C:\Path\RegexUtils.dll' WITH PERMISSION_SET = SAFE;
4. 建立函數：
   CREATE FUNCTION dbo.RegexReplace(@input NVARCHAR(MAX), @pattern NVARCHAR(1000), @replacement NVARCHAR(1000), @global BIT = 1, @ignoreCase BIT = 0) RETURNS NVARCHAR(MAX) AS EXTERNAL NAME RegexUtils.UserDefinedFunctions.RegexReplace;

3. 使用範例

-- 移除 HTML 標籤
SELECT dbo.RegexReplace('text', '<[^>]+>', '', 1, 1);
-- 結果: text

-- 標準化電話號碼
SELECT dbo.RegexReplace('Phone: (02) 1234-5678', '\D', '', 1, 0);
-- 結果: 0212345678

效能對比

方案	10萬次調用耗時	安全性	支援長度
VBScript.RegExp (sp_OA)	~45 秒	低 (需 Ole Automation)	8000 字元
CLR Regex	~0.8 秒	高 (SAFE 權限集)	2 GB (MAX)

內建替代 (SQL Server 2016+)

簡單替換用 TRANSLATE、REPLACE、STRING_SPLIT 組合；複雜正則**首選 CLR**。

避坑指南

• 組件權限集：SAFE 足夠 (純計算)，勿用 EXTERNAL_ACCESS 或 UNSAFE
• 正則表達式編譯緩存：靜態 Regex 物件或 RegexOptions.Compiled 提升熱路徑效能
• Azure SQL Managed Instance 支援 CLR，Azure SQL Database **不支援** (需改用應用層處理)

**棄用 sp_OA，擁抱 CLR，正則處理快 50 倍且安全合規。**