SQL Server也能玩正则表达式？二开实现比MySQL更强大的文本处理能力
技术背景：最近在搭建数据仓库，需要通过SQL Server对一系列的激光器提取其功率数，以前使用PowerBI可能是比较好实现的，现在使用SQLServer想实现这一功能还真比较困难，如果SQL Server有PostgreSQL、MySQL一样支持正则表达式就好了。本文通过对SQLServer二开实现比MySQL、PostgreSQL还强大的正则表达式功能一、需求背景
先提个问题，以下表格中请用SQL找出大于等于12000W的记录：
主键激光器1ABC_12200W, 某激光_8000W2切割头_600W, 某激光_100W3某激光器_99000W注：是大于等于12000W
二、思考过程
[*]MySQL、PostgreSQL支持正则表达式，但研究下来，这两数据库中的正则表达也只能用于模式匹配，无法将匹配后的内容进行比较；换言之，MySQL、PostgreSQL应该无法较好的满足对非结构化内容的数值判断。
[*]SQL Server，没有内置的原生正则表达式函数，但可以通过多种方法实现类似功能，如通过C#开发对SQL Server进行功能扩充；
三、实现过程
第一步：开启CLR
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;提示：配置选项 'clr enabled' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。
第二步：编写C#编程集
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
/// <summary>
/// SQL Server扩展函数
/// </summary>
public class SqlRegularExpressions
{
public static SqlBoolean RegexMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull)
return false;
return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
}
}生成SqlServerEx.dll
第三步，在SQL Server中注册程序集SqlServerEx.dll
CREATE ASSEMBLY SqlRegexFROM 'D:\tmp\SqlServerEx\SqlServerEx\bin\Debug\SqlServerEx.dll'WITH PERMISSION_SET = SAFE; 注：可能会提示注册失败，如
针对带有 SAFE 或 EXTERNAL_ACCESS 选项的程序集“SqlServerEx”的 CREATE 或 ALTER ASSEMBLY 失败，因为 sp_configure 的“CLR 严格安全性”选项设置为 1。Microsoft 建议使用其相应登录名具有 UNSAFE ASSEMBLY 权限的证书或非对称密钥为该程序集签名。或者，也可以使用 sp_add_trusted_assembly 信任程序集。
这时需要启用CLR安全设置：
ALTER DATABASE DW SET TRUSTWORTHY ON;
第四步，创建SQL正则表达式函数
/*
Sql Server正则表达式
*/
CREATE FUNCTION f_regex(@input NVARCHAR(MAX), @pattern NVARCHAR(100))
RETURNS BIT
AS EXTERNAL NAME SqlRegex..RegexMatch;第五步，在SQLServer中应用正式表达式
select dbo.f_regex('锐科_3000W','{3,}W')结果如下所示：
到这里SQLServer已经拥有与MySQL、PostgreSQL数据库一样的原生正则表达式函数了
四、总结
[*]通过CLR集成做SQLServer开发，可以对SQLServer功能进行扩充，让其拥有与MySQL、PostgreSQL等支持正式表达式的功能，甚至更强大的功能
[*]实现步骤：1、开启SQLServer的CLR功能；2、编写并编译C#程序集；3、在SQLServer中注册C#编程集；4、编写SQLServer函数对C#编程集进行调用；
graph TD
A[开启SQLServer的CLR功能] --> B[编写并编译C#程序集]
B --> C[在SQLServer中注册C#程序集]
C --> D[编写SQLServer函数对C#程序集进行调用]
[*]下次我们对该功能进行优化，使用其抽取文本值进行比较，类似如下格式：
case when dbo.f_regex_int('锐科_3000W','{3,}W')>12000 then '高功率' else '低功率' end
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]