在最近的工作中遇到了需要在SQL中使用正则表达式来判断字符串是否满足要求,来过滤不符合要求的字符串,在博客中翻阅了一些资料最后总结出了一个解决方案,希望能帮到有需要的人。
1、检查sql server的crl enabled是否开启
在SQL Server中执行
SELECT * FROM sys.configurations WHERE name = 'clr enabled'
语句,得到如下图的结果
若value值为”1”,代表开启,为”0”则表示未开启
若未开启则在SQL Server中执行以下代码
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
2、检查SQL Server主机的.NET版本
进入命令行模式输入以下命令
cd %WINDIR%\Microsoft.NET\Framework\v4.0.30319
MSBuild /version
得到如下图的结果 (我的版本是4.8)
3、创建SQL Server 项目并修改环境
打开Visual Studio 编译器 (这里我用的是2022版本)
创建SQL Server 项目
创建好之后,右键项目点击属性,修改目标平台 (这里我的是SQL Server 2016)
修改目标框架与权限级别
4、新建文件
5、编写代码
下图是C#代码
代码如下
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlBoolean RegexLike(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull)
{
return new SqlBoolean(false);
}
bool result = Regex.IsMatch(input.Value, pattern.Value);
return new SqlBoolean(result);
}
在编写过程中若遇到System.Text.RegularExpressions; 与System.Data.SqlTypes;无法引入,那么请添加程序集,如下图
编写完成后生成解决方案得到dll文件,如下图
6、创建SQL Server数据库的程序集
在对应的数据库中新建查询并写入以下代码并执行
CREATE ASSEMBLY [Regular] --[]中为程序集名称,这个可以自定义
Authorization [dbo]
FROM 'D:\MySQLCRL\MySQLCRL\bin\Debug\MySQLCRL.dll' --这里是之前创建的dll文件的物理路径
WITH PERMISSION_SET = SAFE
7、创建方法
在数据库中执行以下代码
CREATE FUNCTION dbo.fn_Regex ( --fn_Regex为方法名称,可自定义
@input nvarchar(4000), --参数一,你的验证的表达式
@pattern nvarchar(1000) --参数二,你的正则表达式
) RETURNS bit --这个是返回值的类型,返回值的类型要与C#程序的返回值类型对应
AS EXTERNAL NAME Regular.[MySQLCRL.MyCRLRegular].RegexLike;
-- Regular 是之前创建的程序集的名称
-- MySQLCRL 是C#程序的命名空间
-- MyCRLRegular 是C#程序的类型名称
-- RegexLike 是C#程序中的方法名