在SQLServer中使用正则表达式

   sqlserver对于字符串的处理比较弱，replace函数也仅仅支持用字符串来作为匹配项，要是需要使用正则表达式，就只能通过系统存储过程来调用VB组件来实现了，下面是两个写好的正则函数：

测试是否匹配：

GOCREATE FUNCTION dbo.regexIsMatch(@source varchar(5000),   --需要匹配的源字符串@regexp varchar(1000),  --正则表达式@ignorecase bit = 0  --是否区分大小写，默认为false)RETURNS bit  --返回结果-false，-trueASBEGIN--0（成功）或非零数字（失败），是由OLE 自动化对象返回的HRESULT 的整数值。DECLARE @hr integer --用于保存返回的对象令牌，以便之后对该对象进行操作DECLARE @objRegExp integer   DECLARE @objMatches integer--保存结果DECLARE @results bit/*创建OLE 对象实例,只有sysadmin 固定服务器角色的成员才能执行sp_OACreate,并确定机器中有VBScript.RegExp类库*/EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUTIF @hr <> 0 BEGINSET @results = 0RETURN @resultsENDEXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexpIF @hr <> 0 BEGINSET @results = 0RETURN @resultsENDEXEC @hr = sp_OASetProperty @objRegExp, 'Global', falseIF @hr <> 0 BEGINSET @results = 0RETURN @resultsENDEXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecaseIF @hr <> 0 BEGINSET @results = 0RETURN @resultsEND --调用对象方法EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @sourceIF @hr <> 0 BEGINSET @results = 0RETURN @resultsEND--释放已创建的OLE 对象EXEC @hr = sp_OADestroy @objRegExpIF @hr <> 0 BEGINSET @results = 0RETURN @resultsENDRETURN @resultsEND

正则替换：

GO--SQL正则替换函数CREATE FUNCTION dbo.regexReplace ( @source ntext, --原字符串@regexp varchar(1000), --正则表达式@replace varchar(1000), --替换值@globalReplace bit = 1, --是否是全局替换@ignoreCase bit = 0 --是否忽略大小写) RETURNS varchar(1000) AS BEGIN DECLARE @hr integer DECLARE @objRegExp integer DECLARE @result varchar(5000) EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT IF @hr <> 0 BEGIN EXEC @hr = sp_OADestroy @objRegExp RETURN null END EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp IF @hr <> 0 BEGIN EXEC @hr = sp_OADestroy @objRegExp RETURN null END EXEC @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace IF @hr <> 0 BEGIN EXEC @hr = sp_OADestroy @objRegExp RETURN null END EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase IF @hr <> 0 BEGIN EXEC @hr = sp_OADestroy @objRegExp RETURN null END EXEC @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace IF @hr <> 0 BEGIN EXEC @hr = sp_OADestroy @objRegExp RETURN null END EXEC @hr = sp_OADestroy @objRegExp IF @hr <> 0 BEGIN RETURN null END RETURN @result END

    关键是sp_OACreate,sp_OADestroy,sp_OASetProperty,sp_OAMethod这几个存储过程的使用，在《编写扩展存储过程》一文里也有这个用法。

    测试替换函数：print dbo.regexReplace('sfasdf234sdfds', '\D', '', 1, 0)

    使用时需要开启对OLE存储过程的使用：

    exec sp_configure 'Ole Automation Procedures', 1 reconfigure

    在设置此配置时有可能会提示“配置选项'Ole Automation Procedures' 不存在，也可能是高级选项。”的错误，解决办法是在配置之前执行语句：

    exec sp_configure 'show advanced options',1 reconfigure