Regex in SQL Server 2005(ZT)

From http://yogeshyrbyogi.blogspot.com/2009/04/regular-expression-in-sqlserver-2005.html

Step 1 : enable OLE Automation object in SQL Server 2005

Step 2 : Create a function

Step 3: example :

select dbo.RegEx( N'xxx(111)0101', N'(^\w{5}[-]\w{5}[-][\w]*$)|(^\w{4}[(]\d{3}[)][\w-]*$)',0 )


SQL :


use[master]
GO
GRANT EXECUTE ON[sys].[sp_OASetProperty] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OAMethod] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OAGetErrorInfo] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OADestroy] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OAStop] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OACreate] TO[public]
GO
use[master]
GO
GRANT EXECUTE ON[sys].[sp_OAGetProperty] TO[public]
GO
sp_configure 'show advanced options',1
GO
reconfigure
go

exec sp_configure
go
exec sp_configure 'Ole Automation Procedures',1
-- Configuration option ‘Ole Automation Procedures’ changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go



create FUNCTION dbo.RegEx
(
--Expression Target / Source
@Target varchar(5000),
--Regular Expression Pattern
@Pattern varchar(Max),
--whether the expression case sensitive
@CaseSensitive bit = 0
)
RETURNS bit
AS
BEGIN
DECLARE @ReturnOACreate int
DECLARE @ObjToken int
DECLARE @objMatches int
DECLARE @objMatch int
DECLARE @count int
DECLARE @results bit

EXEC @ReturnOACreate = sp_OACreate 'VBScript.RegExp', @ObjToken OUTPUT

--objecttoken OUTPUT
--Is the returned object token, and must be a local variable of data type int.
--This object token identifies the created OLE object and is used in calls to the other
--OLE Automation stored procedures.
--Return
--0 (success) or a nonzero number (failure) that is the integer value of the
--HRESULT returned by the OLE Automation object

IF @ReturnOACreate <> 0 BEGIN
SET @results = 0
RETURN @results
END

EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'Pattern', @Pattern

IF @ReturnOACreate <> 0 BEGIN
SET @results = 0
RETURN @results
END

EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'Global', false
IF @ReturnOACreate <> 0 BEGIN
SET @results = 0
RETURN @results
END

EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'IgnoreCase', @CaseSensitive
IF @ReturnOACreate <> 0 BEGIN
SET @results = 0
RETURN @results
END

EXEC @ReturnOACreate = sp_OAMethod @ObjToken, 'Test', @results OUTPUT, @Target
IF @ReturnOACreate <> 0 BEGIN
SET @results = 0
RETURN @results
END

EXEC @ReturnOACreate = sp_OADestroy @ObjToken
IF @ReturnOACreate <> 0 BEGIN
SET @results = 0
RETURN @results
END
--return 1 for success
RETURN @results
END