SQLServer实现正则表达式

2019独角兽企业重金招聘Python工程师标准>>>

最近一个项目，需要找出不符合格式的数据并修正。我第一时间就想到了正则表达式，开始想写一个程序连接数据库通过程序对字段进行正则验证，但问题是要写一个程序去搜索需要耗费一定时间，这时我想到了如果直接在SQL Server中直接做正则表达式验证可以吗，这样就免去了操作程序的步骤了。

就这个思路我在网上查阅相关资料，总结出有两种方法可以实现：1.用VS编写sqlserver项目，新建自定义函数部署到sqlserver；2.编写存储过程，调用VBScript.RegExp类库进行正则运算。

一、SQLServer项目实现： 

1.我们打开VS，新建“SQLServer数据库项目”。

*这里要注意的是要根据数据库版本去选择.NET Framework版本，我用的是SQL Server 2008 R2，而08 R2支持.NET Framework 3.5及以前的版本，所以我选择的是.NET Framework 3.5。

 

2.在项目中添加新项：SQL CLR C#用户定义的函数

可见除了编写函数外，我们还可以借助SQL CLR编写触发器、储存过程和数据库类型等。

新建后我们就见到熟悉的CS后缀的C#文件。

 

3.使用C#语言编写正则表达式逻辑

 

4.发布到SQL Server。右击项目，选择“发布”

选择好要发布的目标SQL Server数据库，点击发布对函数进行部署。

 

 

*发布操作特别需要注意的是在创建项目是要选择好.NET Framework版本和要配置好项目的“目标平台”，选择好对应的SQL Server版本，不然发布操作是就会像下面一样报错

 

 

当发布操作无误，我们打开SQL Server相应的数据库，我们就可以看到数据库函数库里面多了我们程序一样的函数——Fn_RegularExpression

 

 

---

看样子是大功告成了，那我们就运行一下这个函数看看怎样吧

 

结果报错了，这里要说明一下。因为SQL CLR毕竟对于SQL Server来说是外部的组件，所以为了安全，SQL Server默认禁用SQL CLR的，但我们可以手动打开它

 

5.开启SQL CLR功能：

exec sp_configure 'clr enabled', '1'

但因为CLR属于SQL Server的高级选项，有时候高级选项也是关闭的，所以这时我们就需要先开启高级选项，再开启CLR：

exec sp_configure 'show advanced options', '1';

RECONFIGURE

go

exec sp_configure 'clr enabled', '1'

RECONFIGURE

go

exec sp_configure 'show advanced options', '1';

RECONFIGURE

go  

 

在配置后，我们再次测试一下函数：

这里的正则表达式是匹配简繁中文的，“你好”匹配，所以返回1

 

这里的“Hello”属于英文，不匹配，所以返回0

 

我这里还测试了C# Regex类 的Replace方法

 

 

使用SQL CLR很是方便啊！！！

 

二、调用VBScrip正则类库实现：  

这里就不多作详细说明了，直接上代码

 

CREATE FUNCTION dbo.Fn_RegularExpression

(

  @sqlField NVARCHAR(max),  --需要匹配的源字符串

  @pattern NVARCHAR(max),   --正则表达式

  @isIgnoreCase BIT     --是否区分大小写

)

RETURNS BIT  --返回结果false，true

AS

BEGIN

 

  --0（成功）或非零数字（失败），是由OLE 自动化对象返回的HRESULT 的整数值。

  DECLARE @hr  INT 

 

  --用于保存返回的对象令牌，以便之后对该对象进行操作

  DECLARE @objRegExp INT   DECLARE @objMatches INT

 

  --保存结果

  DECLARE @results BIT

 

  --创建OLE 对象实例

  EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT

  IF @hr <> 0 BEGIN

   SET @results = 0

   RETURN @results

  END

  

  --设置模式

  EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @pattern

  IF @hr <> 0 BEGIN

   SET @results = 0

   RETURN @results

  END

  --设置全局可用性

  EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false

  IF @hr <> 0 BEGIN

   SET @results = 0

   RETURN @results

  END

  --设置忽略大小写

  EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @isIgnoreCase

  IF @hr <> 0 BEGIN

   SET @results = 0

   RETURN @results

  END 

  --调用对象方法

  EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @sqlField

  IF @hr <> 0 BEGIN

   SET @results = 0

   RETURN @results

  END

  --释放OLE 对象

  EXEC @hr = sp_OADestroy @objRegExp

  IF @hr <> 0 BEGIN

   SET @results = 0

   RETURN @results

  END

RETURN @results

END  

 

运行结果跟上一个方法一样，虽然一个脚本运行就行了，但如果机器上不存在这个类库的话就不能使用了。

SQL Server可以通过SQL CLR扩展本来不具备的功能，这样方便了我们工作，也丰富了SQL Server的功能。就跟大家分享到这里吧。

转载于:https://my.oschina.net/venoriee/blog/385962