在SQLServer中使用正则表达式

本文来自:
http://www.cnblogs.com/sdlfx/archive/2011/01/23/1942696.html

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

测试是否匹配：

GO
CREATE FUNCTION dbo.regexIsMatch
(
@source varchar(5000), --需要匹配的源字符串
@regexp varchar(1000), --正则表达式
@ignorecase bit = 0 --是否区分大小写，默认为false
)
RETURNS bit --返回结果-false，-true
AS
BEGIN

--1（成功）,是由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