SQL SERVER 正则匹配实例

create function dbo.RegexMatch (     

@pattern varchar(2000),     

@matchstring varchar(8000) 

) 

returns int

as

begin    

    declare @objRegexExp int    

    declare @strErrorMessage varchar(255)     

    declare @hr int,@match bit    

    exec @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp out    

    if @hr = 0         

        exec @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern     

    if @hr = 0         

        exec @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1     

    if @hr = 0         

        exec @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring     

    if @hr <>0     

    begin        

        return null    

    end    

    exec sp_OADestroy @objRegexExp     

    return @match 

end

 

使用方法：

select name from BS_products  where dbo.RegexMatch('^((https|http|ftp|rtsp|mms)?:\/\/)[^\s]+',Intro)=1 

 

select * from dbo.Bs_ProProperty where dbo.RegexMatch('^\d{6}$',code)=1