用过Oracle的人都知道Oracle有四个正则表达函数REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和EGEXP_REPLACE，而SQLServer却无法完全实现上面的功能。以前我们知道用sp_OAxxx系列函数来调用js组建实现正则，现在我们可以通过CLR扩展来借助.Net实现。
※ 代码很简单，就是封装一下System.Text.RegularExpressions.Regex到SQLProject当中。我们可以用下面15行代码完成上述的四个函数中的最常用的两个REGEXP_LIKE和EGEXP_REPLACE。
using System;
using System.Data.SqlTypes;
public partial class RegExp
{
[Microsoft.SqlServer.Server.SqlFunction ]
public static SqlBoolean RegExp_Like(SqlString input,SqlString pattern)
{
if (input.IsNull || pattern.IsNull) return false ;
return System.Text.RegularExpressions.Regex .IsMatch(input.Value, pattern.Value);
}
[Microsoft.SqlServer.Server.SqlFunction ]
public static SqlString RegExp_Replace(SqlString input,SqlString pattern,SqlString replacement)
{
if (input.IsNull || pattern.IsNull || replacement.IsNull) return input;
return new System.Text.RegularExpressions.Regex (pattern.Value).Replace(input.Value, replacement.Value);
}
};
※ 把上述的代码编译为SQLCLR_RegExp.dll，然后找一台服务器发布，我们这里发布到测试数据库sqlclr中。发布代码如下，比较简单，我就不写注释了，大致流程就是启用clr然后注册assembly，最后申明函数。
create database sqlclr
go
use sqlclr
go
exec sp_configure 'clr enabled' , '1'
go
reconfigure ;
exec sp_configure 'show advanced options' , '1' ;
go
ALTER DATABASE sqlclr SET TRUSTWORTHY On
go
CREATE ASSEMBLY SqlClr_RegEx FROM 'E:/sqlclrdata/SQLCLR_RegExp.dll' WITH PERMISSION_SET = UnSAFE;
--
go
CREATE FUNCTION dbo. ufn_RegExp_Like
(
@input nvarchar ( max ),
@pattern nvarchar ( 4000)
)
RETURNS bit
AS EXTERNAL NAME SqlClr_RegEx. RegExp. RegExp_Like
go
CREATE FUNCTION dbo. ufn_RegExp_Replace
(
@input nvarchar ( max ),
@pattern nvarchar ( 4000),
@replacement nvarchar ( 4000)
)
RETURNS nvarchar ( max )
AS EXTERNAL NAME SqlClr_RegEx. RegExp. RegExp_Replace
※ 这样我们就有两个处理正则表达式的函数 ufn_RegExp_Like和 ufn_RegExp_Replace ，下面做一些测试，比如邮箱格式的检查和数字的替换。
set nocount on
declare @t table ( teststring varchar ( 50))
insert into @t select ' 上海市南京路号弄号 '
insert into @t select 'jinjazz@sina.com.cn'
insert into @t select ' 剪刀 @msn.com'
insert into @t select 'fdf98s'
-- 获取合法邮箱
select * from @t
where dbo. ufn_RegExp_Like( teststring, '/w+([-+.]/w+)*@/w+([-.]/w+)*/./w+([-.]/w+)*' )> 0
/*
teststring
--------------------------------------------------
jinjazz@sina.com.cn
剪刀 @msn.com
*/
-- 替换数字
select dbo. ufn_RegExp_Replace( teststring, '[/d*$]' , '*' ) as newstring from @t
/*
newstring
-------------------------------------------------
上海市南京路 *** 号 * 弄 * 号
jinjazz@sina.com.cn
剪刀 @msn.com
fdf**s
*/
set nocount off