我们用过SQL SERVER的都知道，SQL SERVER它本身是不自带正则表达式的，因为没有，所以基本都没用过啊，
但我们在C#中对文本匹配用正则的方式处理非常好用，省得你写一堆代码实现匹配，多简洁啊，
那么是否想过，在SQL SERVER中也使用正则，可能吗?
答案：当然啦，
自从微软发布SQL SERVER 2008就支持CLR了，这里就是突破口了，可以用C#写正则函数给SQL函数调用了，
这里实现的方法说明如下
一.先说一下工作中实际遇到的问题
PCB流程是顺序结构，流程的顺序是有着严格的要求的，顺序不能错，流程也不能多，更不能少，
所以要保证流程顺序正确，需要检测：流程工序顺序上下顺序是否正确，而SQL SERVER本身是不支持数组的，要实现上下行数据对比，很困难的。
如果实现起来需要堆N多行代码来实现它，这样增加了程序代码实现的复杂度了，而且后面维护的人看这样的代码也吐血，
从这个出发点思考一种新的方式，是否可以写出一种更加简洁的代码来解决此问题呢，大家都能看懂的。
接下来后面讲了就是解决此问题的方法了。
目前的问题点： 我们要检测【钻孔】前工序是否存在【磨边】，同时【钻孔】后第2个工序是否存在【沉铜】工序
对于这样的问题，纯SQL来实现的话，不写N行代码解决不了是吧
二.C# 写SQL SERVER 正则函数代码
这里将常用的4个正则匹配移值到SQL SERVER中，目前想想应该是够用了
////// 正则匹配 /// /// 正则表达式 /// 文本 ///[Microsoft.SqlServer.Server.SqlFunction] public static SqlString RegexMatch(string regex, string input) { return string.IsNullOrEmpty(input) ? "" : Regex.Match(input, regex, RegexOptions.IgnoreCase).Value; } /// /// 正则匹配次数 /// /// 正则表达式 /// 文本 ///[Microsoft.SqlServer.Server.SqlFunction] public static SqlInt32 RegexMatchsCount(string regex, string input) { return Regex.Matches(input, regex, RegexOptions.IgnoreCase).Count; } /// /// 正则替换 /// /// 正则表达式 /// 文本 /// 要替换的目标 ///[Microsoft.SqlServer.Server.SqlFunction] public static SqlString RegexReplace(string regex, string input, string replace) { return string.IsNullOrEmpty(input) ? "" : Regex.Replace(input, regex, replace, RegexOptions.IgnoreCase); } /// /// 正则校验 /// /// 正则表达式 /// 文本 ///[Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean RegexIsMatch(string regex, string input) { return !string.IsNullOrEmpty(input) && Regex.IsMatch(input, regex, RegexOptions.IgnoreCase); }
三.SQL SERVER修改程序集与创建4个正则函数
alter ASSEMBLY SQLfunctionAssembly FROM 'D:\SQLClr.dll' --改为自己C#写的dll路径填写 WITH PERMISSION_SET = UNSAFE; --正则匹配 CREATE FUNCTION[dbo].[RegexMatch] ( @regex NVARCHAR(max), @input NVARCHAR(max) ) RETURNS NVARCHAR(max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLfunctionAssembly].[SQLClr.SQLfunction].[RegexMatch] --[SQL程序集名].[命名空间.类名].[方法名] --正则匹配次数 CREATE FUNCTION[dbo].[RegexMatchsCount] ( @regex NVARCHAR(max), @input NVARCHAR(max) ) RETURNS int WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLfunctionAssembly].[SQLClr.SQLfunction].[RegexMatchsCount] --[SQL程序集名].[命名空间.类名].[方法名] --正则替换 CREATE FUNCTION[dbo].[RegexReplace] ( @regex NVARCHAR(max), @input NVARCHAR(max), @replace NVARCHAR(max) ) RETURNS NVARCHAR(max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLfunctionAssembly].[SQLClr.SQLfunction].[RegexReplace] --[SQL程序集名].[命名空间.类名].[方法名] --正则校验 CREATE FUNCTION[dbo].[RegexIsMatch] ( @regex NVARCHAR(max), @input NVARCHAR(max) ) RETURNS bit WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLfunctionAssembly].[SQLClr.SQLfunction].[RegexIsMatch] --[SQL程序集名].[命名空间.类名].[方法名]
四.正则函数应用例子
应用场景1：
检测：【钻孔】前流程存在【开料】工序 同时后流程存在【外层前处理】工序
DECLARE @reStr VARCHAR(MAX)SET @reStr = '流程指示,计划投料,开料,裁切,磨边,圆角,打标,烘板,钻孔,去毛刺,烘板,沉铜,负片电镀,磨板,外层前处理,贴膜,曝光,显影,酸性蚀刻,退膜,外层AOI,阻焊前处理,丝印,预烘,曝光,显影,字符,终固化,喷砂,板边包胶,沉金,水洗烘干,电子测试,铣板,成品清洗,功能检查,外观检查,内包装,入库,外包装,出库'SELECT dbo.RegexIsMatch('开料.*钻孔',@reStr)SELECT dbo.RegexMatch('开料.*钻孔',@reStr)
应用场景2：
检测：【钻孔】前一个流程存在【烘板】工序 同时后一个流程存在【去毛刺】工序
DECLARE @reStr VARCHAR(MAX)SET @reStr = '流程指示,计划投料,开料,裁切,磨边,圆角,打标,烘板,钻孔,去毛刺,烘板,沉铜,负片电镀,磨板,外层前处理,贴膜,曝光,显影,酸性蚀刻,退膜,外层AOI,阻焊前处理,丝印,预烘,曝光,显影,字符,终固化,喷砂,板边包胶,沉金,水洗烘干,电子测试,铣板,成品清洗,功能检查,外观检查,内包装,入库,外包装,出库'SELECT dbo.RegexIsMatch('烘板,钻孔,去毛刺',@reStr)SELECT dbo.RegexMatch('烘板,钻孔,去毛刺',@reStr) 应用场景3：
检测：【钻孔】前流程存在【开料】工序 同时需满足，【开料】到【钻孔】之间的允许存在其这的工序，数量在3-6个之间
DECLARE @reStr VARCHAR(MAX)SET @reStr = '流程指示,计划投料,开料,裁切,磨边,圆角,打标,烘板,钻孔,去毛刺,烘板,沉铜,负片电镀,磨板,外层前处理,贴膜,曝光,显影,酸性蚀刻,退膜,外层AOI,阻焊前处理,丝印,预烘,曝光,显影,字符,终固化,喷砂,板边包胶,沉金,水洗烘干,电子测试,铣板,成品清洗,功能检查,外观检查,内包装,入库,外包装,出库'SELECT dbo.RegexIsMatch('开料((,[\u4e00-\u9fa5]{1,8}){3,6}),钻孔',@reStr)SELECT dbo.RegexMatch('开料((,[\u4e00-\u9fa5]{1,8}){3,6}),钻孔',@reStr)
六.综合实例
1.重新封装一个函数，客户端只需传入生产型号检测与正则表达式，即可检测流程顺序是否符合要求
此实例用到了【JoinString】聚合函数，可以看下面这篇文章有说明
CREATE FUNCTION [dbo].[IsMatchPpeflowOrderno] (@regex VARCHAR(MAX),@isTechno bit,@pdctno varchar(50))RETURNS BITASBEGIN DECLARE @reStr varchar(5000) IF (@isTechno = 1) BEGIN SELECT @reStr = FP_EMSDB_PUB.dbo.JoinString(TechNo,',',GlobalOrder) FROM FP_EMS_DB.dbo.V_PPEFlow WHERE FlowLevel = 2 AND pdctno = @pdctno GROUP BY pdctno END ELSE BEGIN SELECT @reStr = FP_EMSDB_PUB.dbo.JoinString(techname,',',GlobalOrder) FROM FP_EMS_DB.dbo.V_PPEFlow WHERE FlowLevel = 2 AND pdctno = @pdctno GROUP BY pdctno END SET @reStr = ISNULL(@reStr,'') RETURN dbo.RegexIsMatch(@regex,@reStr)END
2.实际函数调用
检测：【钻孔】到前流程【开料】工序相隔5个工序， 同时需满足，【钻孔】到后流程【沉铜】工序相隔2个工序，
此是检测流程名称是汉字：正则式用[\u4e00-\u9fa5] 如果是流程编码：正则式改为：\w
SELECT [dbo].[IsMatchPpeflowOrderno]('开料((,[\u4e00-\u9fa5]{1,8}){5,5}),钻孔((,[\u4e00-\u9fa5]{1,8}){2,2}),沉铜',0,'2G16G00LA0')
七.小结
首次将正则表达式移值到存储过程中来，和在C#中用法一样，效率方面可以接受,总体来说表现不错的.
在工作中有写存储过程可以直接嵌套此函数加以应用了哦，对文本处理对比SQL来写的话，更加简洁了;后续维护也方便。
这次移值进来表示非常满意，测试一次成功到位。