SQL CLR函數（以正則表達式為例）

2024-07-21 02:52:34

字體：大中小

來源：轉載

供稿：網友

創建CLR程序集

VS中新建項目，類型為Database，模板選擇SQL Server項目，然後在解決方案資源管理器中，選中項目，右鍵添加，選擇“用戶定義的函數“，然後在類文件中寫入相關代碼，最後生成即可。以下是以正則創建正則表達式相關函數的代碼為例：

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text.RegularExPRessions;using System.Collections;public partial class SqlRegularExpressions{ // 是否匹配 [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean RegExpIsMatch(string input,string pattern) { return Regex.IsMatch(input, pattern); } // 第一個匹配的字符串 [Microsoft.SqlServer.Server.SqlFunction] public static SqlString RegExpMatch(string input, string pattern) { return Regex.Match(input, pattern).Value; } // 所有匹配的字符串 [SqlFunction(FillRowMethodName = "FillMatch" ,TableDefinition = "[index] int, length int, value nvarchar(4000)") ] public static IEnumerable RegExpMatches(string input, string pattern) { return Regex.Matches(input, pattern); } // 與上面配套的填充函數 public static void FillMatch(object obj, out int index,out int length, out SqlChars value) { Match match = (Match)obj; index = match.Index; length = match.Length; value = new SqlChars(match.Value); } // 正則替換 [Microsoft.SqlServer.Server.SqlFunction] public static SqlString RegExpReplace(string input, string pattern, string replacement) { return Regex.Replace(input, pattern, replacement); }}

部署CLR函數

可以直接用VS部署（生成－＞部署），也可以用以下代碼手動創建：第一步，創建程序集

CREATE ASSEMBLY SqlRegularExpressions from 'E:/Study/DotNet/SqlServer_RegExp/bin/Debug/sqlRegExpClass.dll' WITH PERMISSION_SET = SAFEGo

第二步，創建引用程序集的函數

-- Regular Expression LikeCREATE FUNCTION dbo.RegExp_Like( @Input NVARCHAR(MAX), @Pattern NVARCHAR(500))RETURNS BITAS EXTERNAL NAME -- [ASSEMBLY Name].[Class Name].[Function Name]RegularExpression.SqlRegularExpressions.RegExpIsMatch;Go-- First Match SubstringCREATE FUNCTION dbo.RegExp_FirstMatch( @Input NVARCHAR(MAX), @Pattern NVARCHAR(500))RETURNS NVARCHAR(MAX)AS EXTERNAL NAME RegularExpression.SqlRegularExpressions.RegExpMatch;Go-- All Matched SubstringCREATE FUNCTION dbo.RegExp_Matches( @Input NVARCHAR(MAX), @Pattern NVARCHAR(500))RETURNS Table([Index] INT,[Length] INT,[Value] NVARCHAR(500))AS EXTERNAL NAME RegularExpression.SqlRegularExpressions.RegExpMatches;Go-- Regular Expression RepalceCREATE FUNCTION dbo.RegExp_Replace( @Input NVARCHAR(MAX), @Pattern NVARCHAR(500), @Replacement NVARCHAR(500))RETURNS NVARCHAR(MAX)AS EXTERNAL NAME RegularExpression.SqlRegularExpressions.RegExpReplace;GO

使用舉例

-- 是否含有漢字SELECT dbo.RegExp_Like(N'我是帥哥','[/u4e00-/u9fa5]');-- 第一個含有字母z的單詞SELECT dbo.RegExp_FirstMatch('ablaze beagle choral dozen elementary fanatic','/b/w*z+/w*/b');-- 所有含有字母z的單詞SELECT * FROM dbo.RegExp_Matches('ablaze beagle choral dozen elementary fanatic','/b/w*z+/w*/b');

運行結果：