CLR 整合：在 SQL Server 中使用正則表達式函數

背景動機

T-SQL 字串處理能力弱，缺乏原生正則表達式支援。SQL Server 2005+ 引入 CLR 整合，可用 C#/VB.NET 撰寫高效能字串處理函數並註冊到資料庫。

C# 程式碼：正則分割字串為資料列 (Table-Valued Function)

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

public class CLR_FUNCTION
{
    /// 

    /// 依正則表達式分割字串，輸出每一比對結果為一列
    /// 範例：輸入 '1,2,3,4,5,12,8'，Pattern '\d{1,2}' → 輸出 1,2,3,4,5,12,8 各一列
    /// 
    [SqlFunction(
        TableDefinition = "tmp_value nvarchar(max)",
        FillRowMethodName = "SplitByRegex_FillRow"
    )]
    public static IEnumerable SplitStrByRegex(SqlString input, SqlString pattern)
    {
        var results = new List();
        if (input.IsNull || pattern.IsNull) return results;
        
        var matches = Regex.Matches(input.Value.Trim(), pattern.Value.Trim());
        foreach (Match m in matches)
        {
            if (m.Success) results.Add(m.Value);
        }
        return results;
    }

    // FillRow 方法：將 IEnumerable 元素轉為 SqlString 輸出
    public static void SplitByRegex_FillRow(object obj, out SqlString tmp)
    {
        tmp = new SqlString(obj.ToString());
    }
}

部署步驟

1. 建立 Class Library 專案，目標 .NET Framework 3.5/4.x
2. 參考 Microsoft.SqlServer.Server (在 SQL Server 安裝目錄 SDK 資料夾)
3. 編譯產生 DLL (如 ClrRegex.dll)
4. 在 SSMS 中部署：

   
   -- 1. 啟用 CLR (執行個體層級)
   EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
   
   -- 2. 建立組件 (需 SAFE/EXTERNAL_ACCESS/UNSAFE 權限)
   CREATE ASSEMBLY ClrRegex 
   FROM 'C:\Deploy\ClrRegex.dll' 
   WITH PERMISSION_SET = SAFE;
   
   -- 3. 建立函數對應
   CREATE FUNCTION dbo.SplitByRegex (@input NVARCHAR(MAX), @pattern NVARCHAR(MAX))
   RETURNS TABLE (tmp_value NVARCHAR(MAX))
   AS EXTERNAL NAME ClrRegex.[CLR_FUNCTION].SplitStrByRegex;
   

5. 測試：

   
   SELECT * FROM dbo.SplitByRegex('1,2,3,4,5,12,8', '\d{1,2}');
   -- 結果：1, 2, 3, 4, 5, 12, 8 (各一列)
   

權限集合選擇

效能與最佳實務

• CLR 函數適用於CPU 密集型字串運算 (正則、解析、編碼)
• 避免在 CLR 中進行資料庫存取 (Context Connection 除外)
• 大量資料集考慮 SqlDataReader 流式處理而非記憶體集合
• 部署 DLL 至資料庫內部，避免外部檔案相依
• 嚴格測試：例外處理、Null 輸入、效能基準測試