正则表达式 第六篇：调用CLR函数执行正则查询

　　正则表达式在文本查询方面，不管是速度还是功能，都十分强大。虽然SQL Server数据库可以执行模糊查询（像like子句）和全文查询（Fulltext search），但是这两个子句只能查询简单的模式，无法应对复杂的查询需求。这是因为SQL Server没有执行正则表达式的内置函数，无法直接执行正则查找。我们可以创建CLR标量函数，在函数中调用正则表达式，把CLR函数发布到SQL Server数据库中，这样，就可以通过TSQL脚本调用CLR函数来执行复杂的正则查询和匹配。
　　
　　一，Regex类
　　
　　Regex类用于表示一个正则表达式，执行匹配、替换和拆分操作，Regex类有五大方法：
　　
　　IsMatch()：是否匹配到正则
　　
　　Match()：返回正则的第一个匹配
　　
　　Matches()：返回正则的全部匹配
　　
　　Replace()：把匹配正则表达式的文本替换掉
　　
　　Split()：把输入文本拆分，拆分的边界是匹配正则表达式的文本
　　
　　1，创建Regex 对象
　　
　　创建Regex对象，并指定正则选项（忽略大小写）：
　　
　　Regex re = new Regex("(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase);
　　
　　string mat = re.Match(input_text).Value;
　　
　　也可以直接使用静态方法，直接获取到第一个匹配的值：
　　
　　string mat = Regex.Match(input_txt,"(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase).Value;
　　
　　2，查找匹配
　　
　　按照正则来查看匹配的文本是正则表达式最常用的功能，
　　
　　复制代码
　　
　　Regex re = new Regex("(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase);
　　
　　MatchCollection mc = re.Matches(text_input);
　　
　　foreach(Match mt in mc)
　　
　　{
　　
　　//mt.Value
　　
　　}
　　
　　复制代码
　　
　　二，创建CLR工程
　　
　　我使用的IDE版本是VS2017 Enterprise，要创建CLR工程，首先需要创建SQL Server 类型的 Project。
　　
　　1，新建CLR函数
　　
　　在已创建的SQL Server Project中添加新项目（Add -> New Item)，选项SQL CLR C# User Defined Function，这里把文件命名为UserDefinedFunctions.cs。
　　
　　2，编写CLR代码
　　
　　完整的CLR标量函数示例代码如下，Build 该文件，生成DLL文件，用该DLL文件创建程序集。
　　
　　为了使用正则表达式，需要在文件中添加引用 ： using System.Text.RegularExpressions;
　　
　　复制代码
　　
　　using System;
　　
　　using System.Data;
　　
　　using System.Data.SqlClient;
　　
　　using System.Data.SqlTypes;
　　
　　using Microsoft.SqlServer.Server;
　　
　　using System.Text;
　　
　　using System.Text.RegularExpressions;
　　
　　public partial class UserDefinedFunctions
　　
　　www.jintianxuesha.com
　　
　　[Microsoft.SqlServer.Server.SqlFunction]
　　
　　public static SqlString Match(string input, string pattern)
　　
　　{
　　
　　string str = Regex.Match(input, pattern, RegexOptions.IgnoreCase).Value;
　　
　　return new SqlString (str);
　　
　　}
　　
　　public static SqlBoolean IsMatch(string input, string pattern)
　　
　　{
　　
　　bool match = Regex.IsMatch(input, pattern, RegexOptions.IgnoreCase);
　　
　　return new SqlBoolean(match);
　　
　　}
　　
　　public static SqlString Matches(string input, string pattern)
　　
　　{
　　
　　MatchCollection mc = Regex.Matches(input, pattern, RegexOptions.IgnoreCase);
　　
　　StringBuilder strList = new StringBuilder();
　　
　　int idx = 0;
　　
　　foreach(Match m in mc)
　　
　　{
　　
　　strList.Append(string.Format("\"idx{0}\":\"{1}\",", idx, m.Value));
　　
　　idx = idx + 1;
　　
　　}
　　
　　return new SqlString www.yongshenyul.com(strList.ToString());
　　
　　}
　　
　　public static SqlString SplitItem(string input, string separator, int idx)
　　
　　{
　　
　　string[] str = input.Split(new string[www.lannxinzc.cn ] { separator }, StringSplitOptions.RemoveEmptyEntries);
　　
　　return str.Length> idx ? str[idx] : "";
　　
　　}
　　
　　public static string GetJsonItem(string input, string key)
　　
　　{
　　
　　string pattern = string.Format("(?<=\"{0}\":\").*?(?=\")", key);
　　
　　return Regex.Match(input, pattern, RegexOptions.IgnoreCase).Value;
　　
　　}
　　
　　}
　　
　　复制代码
　　
　　三，在SQL Server中创建CLR函数
　　
　　要在SQL Server数据库中创建CLR函数，必须配置SQL Server的选项，然后使用DLL文件创建Assembly，并从Assembly创建SQL 函数。
　　
　　1，配置SQL Server的选项
　　
　　为了把CLR工程部署到SQL Server数据库中，需要配置数据库的高级选项，主要是禁用clr strict security 和启用clr enabled选项。
　　
　　复制代码
　　
　　exec sp_configure 'show advanced options', 1
　　
　　go
　　
　　reconfigure;
　　
　　go
　　
　　exec sp_configure www.tongyayule.com'clr strict security', 0;
　　
　　go
　　
　　reconfigure;
　　
　　go
　　
　　exec sp_configure 'clr enabled', 1
　　
　　go
　　
　　reconfigure
　　
　　go
　　
　　复制代码
　　
　　2，创建程序集
　　
　　引用CLR Project生成的DLL文件，用该DLL文件来创建SQL Server程序集：
　　
　　CREATE ASSEMBLY [SQLServerDatabase]
　　
　　FROM 'E:\clr_project_path.dll'
　　
　　WITH PERMISSION_SET =www.zheshengjpt.com SAFE
　　
　　GO
　　
　　3，从程序集中创建SQL函数
　　
　　把SQL Server Database Project中的创建的函数，逐个创建为SQL函数。
　　
　　复制代码
　　
　　CREATE FUNCTION [dbo].[Match](@input [nvarchar](max), @pattern [nvarchar](max))
　　
　　RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
　　
　　AS
　　
　　EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[Match]
　　
　　GO
　　
　　CREATE FUNCTION [dbo].[IsMatch](@input [nvarchar](max), @pattern [nvarchar](max))
　　
　　RETURNS bit WITH EXECUTE AS CALLER
　　
　　AS
　　
　　EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[IsMatch]
　　
　　GO
　　
　　CREATE FUNCTION [dbo].[Matches](@input [nvarchar](max), @pattern [nvarchar](max))
　　
　　RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
　　
　　AS
　　
　　EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[Matches]
　　
　　GO
　　
　　CREATE FUNCTION [dbo].[SplitItem](@input [nvarchar](max), @separator [nvarchar](max), @idx int)
　　
　　RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
　　
　　AS
　　
　　EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[SplitItem]
　　
　　GO
　　
　　CREATE FUNCTION [dbo].[GetJsonItem](@input [nvarchar](max), @key [nvarchar](max))
　　
　　RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
　　
　　AS
　　
　　EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[GetJsonItem]
　　
　　GO
　　
　　复制代码
　　
　　在SQL函数创建之后，就可以像调用普通函数那样来调用CLR函数。
　　
　　复制代码
　　
　　update [dbo].[DimProductPath]
　　
　　set  ProductPath_ProductFamily=dbo.SplitItem(ProductPath,'/',0)
　　
　　,ProductPath_ProductName=dbo.SplitItem(ProductPath,'/',1)
　　
　　,ProductPath_ProductVersion=dbo.SplitItem(ProductPath,'/',2)
　　
　　,ProductPath_SupportTopic=dbo.SplitItem(ProductPath,'/',3)
　　
　　,ProductPath_SupportSubtopic=dbo.SplitItem(ProductPath,'/',4)
　　
　　复制代码
　　
　　参考文档：
　　
　　C#中正则表达式的使用
　　
　　C# 正则表达式 菜鸟教程
　　
　　Regular Expression Language - Quick Reference