在SQL Server中使用正则表达式

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

测试是否匹配：

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