正则表达式在文本查询方面,不管是速度还是功能,都十分强大。虽然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
正则表达式 第六篇:调用CLR函数执行正则查询
最新推荐文章于 2024-04-23 10:10:56 发布