Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Use of regular expressions in TSQL -- version 2

By Bert De Haes, 2004/09/21

This script creates 2 new system functions ****************************************** NEW: 1. functions will be case sensitive if database is 2. Replace all or only first occurence ****************************************** 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 an sp_OA procedure null if one of the paremeters is null the result of the replace

/*
**	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