sqlserver对于字符串的处理比较弱,replace函数也仅仅支持用字符串来作为匹配项,要是需要使用正则表达式,就只能通过系统存储过程来调用VB组件来实现了,下面是两个写好的正则函数:
测试是否匹配:
GO CREATE FUNCTION dbo.regexIsMatch ( @source varchar(5000), --需要匹配的源字符串 @regexp varchar(1000), --正则表达式 @ignorecase bit = 0 --是否区分大小写,默认为false ) RETURNS bit --返回结果-false,-true AS BEGIN --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 OUTPUT IF @hr <> 0 BEGIN SET @results = 0 RETURN @results END EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp 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', @ignorecase IF @hr <> 0 BEGIN SET @results = 0 RETURN @results END --调用对象方法 EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source 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
正则替换:
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