SQL Server 2005 CLR 使用者定義函數實作正則表達式支援

動機

T-SQL 原生不支援正則表達式，透過 SQLCLR 封裝 System.Text.RegularExpressions.Regex 即可在查詢中直接使用 Regex 比對、擷取、替換。

建立專案與 UDF 步驟

1. VS → SQL Server Database Project → 加入「User-Defined Function」項目。
2. 實作靜態方法，標記 [SqlFunction]：

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

public partial class UserDefinedFunctions
{
    public static readonly RegexOptions Options = 
        RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline;

    [SqlFunction]
    public static SqlString RegexMatch(SqlChars input, SqlString pattern)
    {
        if (input.IsNull || pattern.IsNull) return SqlString.Null;
        var regex = new Regex(pattern.Value, Options);
        var match = regex.Match(new string(input.Value));
        return match.Success ? new SqlString(match.Value) : SqlString.Null;
    }

    [SqlFunction]
    public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
    {
        if (input.IsNull || pattern.IsNull) return SqlString.Null;
        var regex = new Regex(pattern.Value, Options);
        return new SqlString(regex.Replace(new string(input.Value), replacement.Value));
    }

    [SqlFunction(FillRowMethodName = "FillMatches")]
    public static System.Collections.IEnumerable RegexMatches(SqlChars input, SqlString pattern)
    {
        if (input.IsNull || pattern.IsNull) yield break;
        var regex = new Regex(pattern.Value, Options);
        foreach (Match m in regex.Matches(new string(input.Value)))
            yield return m.Value;
    }
    public static void FillMatches(object obj, out SqlString value) 
        => value = new SqlString((string)obj);
}

部署與測試

1. 專案屬性設定目標資料庫，Build → Deploy。
2. SSMS 測試：

-- 比對電子郵件
SELECT dbo.RegexMatch(CAST(email AS SQLCHARS), N'\w+@\w+\.\w+') FROM Customers;

-- 替換電話格式
SELECT dbo.RegexReplace(CAST(phone AS SQLCHARS), N'(\d{3})(\d{4})(\d{4})', N'$1-$2-$3') FROM Contacts;

-- 擷取所有數字
SELECT * FROM dbo.RegexMatches(CAST(notes AS SQLCHARS), N'\d+');

效能與安全考量

• 組件權限集設為 SAFE (純運算，無外部存取)。
• 編譯 Regex 物件有成本，靜態 readonly 欄位快取 RegexOptions，避免重複編譯相同模式可考慮 Regex.CompileToAssembly 預編譯。
• 大量資料列呼叫會產生大量 CLR 呼叫開銷，關鍵路徑考慮 T-SQL LIKE / PATINDEX 替代簡單模式。
• 輸入長度超過 8000 字元需使用 SqlChars 流式讀取，避免記憶體壓力。

進階：表值函數回傳比對群組

[SqlFunction(TableDefinition = "MatchValue NVARCHAR(4000), Group1 NVARCHAR(4000), Group2 NVARCHAR(4000)",
    FillRowMethodName = "FillMatchGroups")]
public static IEnumerable RegexMatchGroups(SqlChars input, SqlString pattern) { ... }

public static void FillMatchGroups(object row, out SqlString v1, out SqlString v2, out SqlString v3) { ... }