在SQLServer中使用正则表达式

测试是否匹配：

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