Use of regular expressions in TSQL -- version 2

/*
**Author : Bert De Haes
**Date : 2004-04-06
**
**This script creates 2 new system functions :
**
**fn__regexp_test(@String,@Pattern) returns int
**execute the method test(@String) on the regular expressions @Pattern
**returns :
**0 : no match
**1 : match
**null : one of the parameters was null
**anything else : error from sp_OA procedure
**
**fn__regexp_replace(String,@Pattern,@Replace,@Global) returns varchar
**execute the method Replace(@String,@Replace) on the regular expressions @Pattern
**Global = 1 ( Default ) replaces all occurences, Global = 0 only the first occurance
**returns :
**null on failure of a sp_OA procedure
**null if one of the paremeters is null
**the result of the replace
**
*/go
set nocount on
go
use master
go
sp_configure 'allow updates',1
go
RECONFIGURE WITH OVERRIDE
go
if exists ( select 1 from sysobjects where name = 'fn__regexp_test' )
drop function system_function_schema.fn__regexp_test
go
create function system_function_schema.fn__regexp_test
(
@String varchar(4000),
@Pattern varchar(255)
)  
returns int -- 0 if no match, 1 if match, other values on failure
as  
begin  
declare @ret int, @ret2 int  
declare @regexp int  
declare @result int 
declare @IgnoreCase int

-- get IgnoreCase form current collation settings
set @IgnoreCase = case when 'x' = 'X' then 1 else 0 end

if @String is null or @Pattern is null return null

-- create object vbscript.regexp. return errorcode on failure
exec @ret = sp_OACreate 'VBScript.RegExp', @regexp output  
if @ret <> 0 return case when @ret = 1 then -1 else @ret end

-- set property Pattern. go to FINI on failure ( will destroy object )
exec @ret = sp_OASetProperty @regexp, 'Pattern', @Pattern  
if @ret <> 0 goto FINI

-- set property IgnoreCase. go to FINI on failure ( will destroy object )
exec @ret = sp_OASetProperty @regexp, 'IgnoreCase', @IgnoreCase
if @ret <> 0 goto FINI

-- exec method Test. go to FINI on failure
exec @ret = sp_OAMethod @regexp, 'Test', @result output, @String  
if @ret <> 0 goto FINI

FINI:
-- destroy created object
exec @ret2 = sp_OADestroy @regexp  

-- if failure before destroy, return failure status
if @ret  <> 0 return case when @ret  = 1 then -1 else @ret  end
-- if failure on destry, return destroy failure status
if @ret2 <> 0 return case when @ret2 = 1 then -1 else @ret2 end

-- return the result
return @result 
end  
go
if exists ( select 1 from sysobjects where name = 'fn__regexp_replace' )
drop function system_function_schema.fn__regexp_replace
go
create function system_function_schema.fn__regexp_replace
(
@String varchar(4000),
@Pattern varchar(255),
@Replace varchar(4000),
@Global bit = 1
)  
returns varchar(4000)  -- replaced string on success, null on failure
as  
begin  
declare @ret int, @ret2 int
declare @regexp int  
declare @result int
declare @IgnoreCase int

-- get IgnoreCase form current collation settings
set @IgnoreCase = case when 'x' = 'X' then 1 else 0 end 

-- return null if one of the parameters is null
if @String is null or @Pattern is null or @Replace is null return null

-- create object vbscript.regexp. return null on failure
exec @ret = sp_OACreate 'VBScript.RegExp', @regexp output  
if @ret <> 0 return null 

-- set property Pattern. go to FINI on failure ( will destroy object )
exec @ret = sp_OASetProperty @regexp, 'Pattern', @Pattern  
if @ret <> 0 goto FINI 

-- set property IgnoreCase. go to FINI on failure ( will destroy object )
exec @ret = sp_OASetProperty @regexp, 'IgnoreCase', @IgnoreCase
if @ret <> 0 goto FINI

-- set property Global. go to FINI on failure ( will destroy object )
exec @ret = sp_OASetProperty @regexp, 'Global', @Global
if @ret <> 0 goto FINI

-- exec method Test. go to FINI on failure
exec @ret = sp_OAMethod @regexp, 'Test', @result output, @String  
if @ret <> 0 goto FINI

-- if no match : done ( no replace needed )
if @result <> 1 goto FINI

-- exec method Replace. go to FINI on failure
exec @ret = sp_OAMethod @regexp, 'Replace', @String output, @String, @Replace  
if @ret <> 0 goto FINI

FINI:
exec @ret2 = sp_OADestroy @regexp 

-- if failure, return null
if @ret <> 0 return null  
-- if failure for destroy, return null
if @ret2 <> 0 return null

-- return replaced string
return @String  
end  
go
grant exec on system_function_schema.fn__regexp_test to public
grant exec on system_function_schema.fn__regexp_replace to public
go
sp_configure 'allow updates',0
go
RECONFIGURE WITh OVERRIDE
go
print 'test fn__regexp_test'
go
if object_id('tempdb..#fn__regexp_test') is not null drop table #fn__regexp_test
create table #fn__regexp_test (String varchar(50),Pattern varchar(50))
insert #fn__regexp_test values ( 'This is a test' , '^T.* is +.*' )
insert #fn__regexp_test values ( 'This is a test' , '^T.* is  +.*' )
insert #fn__regexp_test values ( 'This is a test' , '^t.* is +.*' )
select*,fn__regexp_test = fn__regexp_test(String,Pattern) from #fn__regexp_test
drop table #fn__regexp_test
go
print 'test fn__regexp_replace'
go
if object_id('tempdb..#fn__regexp_replace') is not null drop table #fn__regexp_replace
create table #fn__regexp_replace (String varchar(50),Pattern varchar(50),Replace varchar(50))
insert #fn__regexp_replace values ( 'This isis a test' , ' is[^ ]*' , ' is' )
insert #fn__regexp_replace values ( 'This is a test' , 'SQL' , 'sql' )
insert #fn__regexp_replace values ( 'This is a test' , 'This' , 'this' )
insert #fn__regexp_replace values ( 'This is a test' , 'THIS' , 'this' )
insert #fn__regexp_replace values ( 'This is a test' , 'is' , 'IS' )
insert #fn__regexp_replace values ( '016/68.15.27'   , '[^0-9]','' )
select*,Global=0,fn__regexp_replace = fn__regexp_replace(String,Pattern,Replace,0) from #fn__regexp_replace
select*,Global=1,fn__regexp_replace = fn__regexp_replace(String,Pattern,Replace,1) from #fn__regexp_replace
drop table #fn__regexp_replace
go