【交流】SQL2005CLR函数扩展-正则表达式

[jinjazz]

blog地址，欢迎大家访问我的博客。
http://blog.csdn.net/jinjazz/archive/2009/04/22/4101746.aspx



用过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 '上海市南京路100号2弄3号'
insert into @t select '[e-mail]'
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
--------------------------------------------------
[e-mail]
剪刀@msn.com
*/


--替换数字
select dbo.ufn_RegExp_Replace(teststring,'[\d*$]','*') as newstring from @t
/*
newstring
-------------------------------------------------
上海市南京路***号*弄*号
[e-mail]
剪刀@msn.com
fdf**s
*/

set nocount off

 

[十八道胡同]

谢谢分享，

[wanshichen]

收藏~

[pelasido]

regex

[playwarcraft]

先mark

[chenminde09]

学习了

[我为鱼肉]

还没学，不懂。。。学习。。。

[walnut2800]

学习

[冰岛男孩]

学习中……

[ycg_893]

不错

[zhangle861010]

学习！

[fescort]

学习

[pwx1983]

学习

[shimenghui]

谢谢了。 沙发

[claro]

/*一直有个疑问，CSDN数据库版的高手都是负责开发吗？
70%的问题都涉及语句构造和重造，15%的问题是单机数据库故障疑问，5%的问题是服务器数据库故障，
10%才是DBA需要做的事情。
渐渐感觉在MSSQL平台上，DBA主要做开发?
*/

[qgylovelj]

up

[bhtfg538]

说一句，LZ 完全没有考虑 正则异常的问题
比如 我 的括号不匹配 怎么办？(.*( 这样的正则 你怎么弄的，很明显 try
以后要返回一个false


使用这个 ，必须考虑 各种情况，
LZ 很热心，不过使用 各种东西的是要要考虑墨菲法则

技术风险能够由可能性变为突发性的事实

[bhtfg538]

没有IDE,稍微修改下

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean RegExp_Like(SqlString input,SqlString pattern)
    {

            try
            {
                if (string.IsNullOrEmpty(input) || string.IsNullOrEmpty(pattern)) return false;
                Regex reg = new Regex(pattern, RegexOptions.Compiled);
                return reg.IsMatch(input);
            }
            catch (ArgumentException ex)
            {
                return false;
            }
            catch (Exception ex)
            {
                return false;
            }

        }

[jcno1]

学习一下

[mingyicz]

mark

[cailee]

mark 掘坟