RegExp find

-- =============================================
-- Create table function (TF)
-- =============================================
IF EXISTS (SELECT * 
   FROM   sysobjects 
   WHERE  name = N'fnRexExpFind')
DROP FUNCTION fnRexExpFind
GO



CREATE   FUNCTION fnRexExpFind 
(
@PaternVARCHAR(8000), 
@strTEXT
)
RETURNS @Result TABLE 
(
NRNUMERIC,
MatchVARCHAR ( 4096 ),
MIndexNUMERIC,
MLenNUMERIC,
SubMatch1VARCHAR ( 256 ),
SubMatch2VARCHAR ( 256 ),
SubMatch3VARCHAR ( 256 ),
SubMatch4VARCHAR ( 256 ),
SubMatch5VARCHAR ( 256 ),
SubMatch6VARCHAR ( 256 ),
SubMatch7VARCHAR ( 256 ),
SubMatch8VARCHAR ( 256 ),
SubMatch9VARCHAR ( 256 )
)
AS
BEGIN


DECLARE @hr    INT  

DECLARE @regExpINT
DECLARE@descVARCHAR( 8000 )
DECLARE @matchesINT
DECLARE @matchINT
DECLARE @submatchesINT
DECLARE @submatchINT


DECLARE@bMatchBIT

DECLARE@DoingVARCHAR( 256 )


DECLARE@tmpCharVARCHAR( 8000 )
DECLARE@tmpNumINT
DECLARE@tmpNum2INT

DECLARE@iNumNUMERIC
DECLARE@CountINT

DECLARE@iNum2NUMERIC
DECLARE@Count2INT



SET@Doing= 'Create RegExp object.'
EXEC@hr= master.dbo.sp_OACreate 'VBScript.RegExp', @regExp OUTPUT  
IF @hr <> 0
GOTO Error

SET@Doing= 'Set pattern.'
EXEC@hr= sp_OASetProperty @regExp, 'Pattern', @Patern
IF @hr <> 0
GOTO Error

SET@Doing= 'Set Global property.'
EXEC@hr= sp_OASetProperty @regExp, 'Global', 'true'
IF @hr <> 0
GOTO Error

SET@Doing= 'Set IgnoreCase property.'
EXEC@hr= sp_OASetProperty @regExp, 'IgnoreCase', 'true'
IF @hr <> 0
GOTO Error

SET@Doing= 'Set Multiline property.'
EXEC@hr= sp_OASetProperty @regExp, 'Multiline', 'true'
IF @hr <> 0
GOTO Error

SET@Doing= 'Executing "test" method.'
EXEC@hr= sp_OAMethod @regExp, 'Test', @bMatch OUTPUT, @str
IF @hr <> 0
GOTO Error

IF @bMatch = 1
BEGIN
SET@Doing= 'Executing "Execute" method.'
EXEC@hr= sp_OAMethod @regExp, 'Execute', @matches OUTPUT, @str
IF @hr <> 0
GOTO Error


SET@Doing= 'Executing "Count" method.'
EXEC@hr= sp_OAMethod @matches, 'Count', @Count OUTPUT
IF @hr <> 0
GOTO Error
SET @iNum= 0
WHILE @iNum < @Count
BEGIN
INSERT INTO @Result(NR)
VALUES(@iNum)

SET@Doing= 'Creating match object.'
SET@tmpCHAR= 'Item(' + CAST(@iNum AS VARCHAR) + ')'
EXEC@hr= sp_OAMethod @matches, @tmpCHAR, @match OUTPUT
IF @hr <> 0
GOTO Error


SET@Doing= 'Get "Value" property.'
EXEC@hr= sp_OAGetProperty @match, 'Value', @tmpCHAR OUTPUT
IF @hr <> 0
GOTO Error

UPDATE@Result
SETMatch= @tmpCHAR
WHERENR= @iNum


SET@Doing= 'Get "FirstIndex" property.'
EXEC@hr= sp_OAGetProperty @match, 'FirstIndex', @tmpNum OUTPUT
IF @hr <> 0
GOTO Error

UPDATE@Result
SETMIndex= @tmpNum
WHERENR= @iNum

SET@Doing= 'Get "Length" property.'
EXEC@hr= sp_OAGetProperty @match, 'Length', @tmpNum2 OUTPUT
IF @hr <> 0
GOTO Error
UPDATE@Result
SETMLen= @tmpNum2
WHERENR= @iNum

SET@Doing= 'Creating submatches object.'
EXEC@hr= sp_OAMethod @match, 'SubMatches', @submatches OUTPUT
IF @hr <> 0
GOTO Error


EXEC@hr= sp_OAMethod @submatches, 'Count' , @Count2 OUTPUT
IF @hr <> 0
GOTO Error

SET @iNum2= 0
WHILE @iNum2 < @Count2
BEGIN
SET@Doing= 'Creating submatch object.'
SET@tmpCHAR= 'Item(' + CAST(@iNum2 AS VARCHAR) + ')'
EXEC@hr= sp_OAMethod @submatches, @tmpCHAR , @tmpCHAR OUTPUT
IF @hr <> 0
GOTO Error

UPDATE@Result
SETSubMatch1= CASE @iNum2 WHEN 0 THEN @tmpCHAR ELSE SubMatch1 END,
SubMatch2= CASE @iNum2 WHEN 1 THEN @tmpCHAR ELSE SubMatch2 END,
SubMatch3= CASE @iNum2 WHEN 2 THEN @tmpCHAR ELSE SubMatch3 END,
SubMatch4= CASE @iNum2 WHEN 3 THEN @tmpCHAR ELSE SubMatch4 END,
SubMatch5= CASE @iNum2 WHEN 4 THEN @tmpCHAR ELSE SubMatch5 END,
SubMatch6= CASE @iNum2 WHEN 5 THEN @tmpCHAR ELSE SubMatch6 END,
SubMatch7= CASE @iNum2 WHEN 6 THEN @tmpCHAR ELSE SubMatch7 END,
SubMatch8= CASE @iNum2 WHEN 7 THEN @tmpCHAR ELSE SubMatch8 END,
SubMatch9= CASE @iNum2 WHEN 8 THEN @tmpCHAR ELSE SubMatch9 END
WHERENR= @iNum

SET @iNum2= @iNum2 + 1
END

SET@Doing= 'Destroy Match object.'
EXEC@hr= master.dbo.sp_OADestroy @match
IF @hr <> 0
GOTO Error

SET @iNum= @iNum + 1
END

SET@Doing= 'Destroy Matches object.'
EXEC@hr= master.dbo.sp_OADestroy @matches  
END

-- IF we get here the normal way, don't do error
GOTO Cleanup
Error:
EXEC sp_OAGetErrorInfo @regExp, @tmpChar OUT, @desc OUT 

INSERT INTO @Result ( NR ,Match )
SELECT0, 
'Error ['+ ISNULL( CAST( convert(varbinary(4),@hr) AS VARCHAR ), '' ) +
'], While ['+ ISNULL( @Doing, '' ) + 
'], Source ['+ ISNULL( @tmpChar, '' ) + 
'], Description ['+ ISNULL( @desc, '' ) + ']'

GOTO Result

Cleanup:
EXEC@hr= master.dbo.sp_OADestroy @regExp  
IF @hr <> 0
GOTO Error

Result: 

RETURN 
END
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT * FROM dbo.fnRexExpFind
( '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.]((?:19|20)\d\d.)', '
Date1: 01/01/2005
Date2: 31-01-2001
Date3: 11.12.2005
')