Use of regular expressions in TSQL

/*
**Author : Bert De Haes
**Date : 20040406
**
**This script creates 2 new system functions :
**
**fn__testregexp(@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__replaceregexp(String,@Pattern,@Replace) returns varchar
**execute the method Replace(@String,@Replace) on the regular expressions @Pattern
**returns :
**null on failure of a sp_OA procedure
**null if one of the paremeters is null
**the result of the replace
**
**If you want the tests to be case insensitive, replace  'IgnoreCase', 0 by  'IgnoreCase', 1 ...
*/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__testregexp' )
drop function system_function_schema.fn__testregexp
go
create function system_function_schema.fn__testregexp
(
@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  

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. goto FINI on failure ( will destroy object )
exec @ret = sp_OASetProperty @regexp, 'Pattern', @Pattern  
if @ret <> 0 goto FINI

-- set property IgnoreCase to false. goto FINI on failure ( will destroy object )
exec @ret = sp_OASetProperty @regexp, 'IgnoreCase', 0
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__replaceregexp' )
drop function system_function_schema.fn__replaceregexp
go
create function system_function_schema.fn__replaceregexp
(
@String varchar(4000),
@Pattern varchar(255),
@Replace varchar(4000)
)  
returns varchar(4000)  -- replaced string on success, null on failure
as  
begin  
declare @ret int, @ret2 int
declare @regexp int  
declare @result int  

-- 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 to false. goto FINI on failure ( will destroy object )
exec @ret = sp_OASetProperty @regexp, 'IgnoreCase', 0
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__testregexp to public
grant exec on system_function_schema.fn__replaceregexp to public
go
sp_configure 'allow updates',0
go
RECONFIGURE WITh OVERRIDE
go
print 'test fn__testregexp'
go
if object_id('tempdb..#fn__testregexp') is not null drop table #fn__testregexp
create table #fn__testregexp (String varchar(50),Pattern varchar(50))
insert #fn__testregexp values ( 'This is a test' , '^T.* is +.*' )
insert #fn__testregexp values ( 'This is a test' , '^T.* is  +.*' )
insert #fn__testregexp values ( 'This is a test' , '^t.* is +.*' )
select*,fn__testregexp = fn__testregexp(String,Pattern) from #fn__testregexp
drop table #fn__testregexp
go
print 'test fn__replaceregexp'
go
if object_id('tempdb..#fn__replaceregexp') is not null drop table #fn__replaceregexp
create table #fn__replaceregexp (String varchar(50),Pattern varchar(50),Replace varchar(50))
insert #fn__replaceregexp values ( 'This isis a test' , ' is[^ ]*' , ' is' )
insert #fn__replaceregexp values ( 'This is a test' , 'SQL' , 'sql' )
insert #fn__replaceregexp values ( 'This is a test' , 'This' , 'this' )
insert #fn__replaceregexp values ( 'This is a test' , 'THIS' , 'this' )
select*,fn__replaceregexp = fn__replaceregexp(String,Pattern,Replace) from #fn__replaceregexp
drop table #fn__replaceregexp
go