RegExp find

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



CREATE   FUNCTION fnRexExpFind 
(
	@Patern		VARCHAR(8000), 
	@str		TEXT
)
RETURNS @Result TABLE 
(
	NR		NUMERIC,
	Match		VARCHAR ( 4096 ),
	MIndex		NUMERIC,
	MLen		NUMERIC,
	SubMatch1	VARCHAR ( 256 ),
	SubMatch2	VARCHAR ( 256 ),
	SubMatch3	VARCHAR ( 256 ),
	SubMatch4	VARCHAR ( 256 ),
	SubMatch5	VARCHAR ( 256 ),
	SubMatch6	VARCHAR ( 256 ),
	SubMatch7	VARCHAR ( 256 ),
	SubMatch8	VARCHAR ( 256 ),
	SubMatch9	VARCHAR ( 256 )
)
AS
BEGIN
	
	
	DECLARE @hr    		INT  
	
	DECLARE @regExp		INT
	DECLARE	@desc		VARCHAR( 8000 )
	DECLARE @matches	INT
	DECLARE @match		INT
	DECLARE @submatches	INT
	DECLARE @submatch	INT
	
	
	DECLARE	@bMatch		BIT
	
	DECLARE	@Doing		VARCHAR( 256 )
	
	
	DECLARE	@tmpChar	VARCHAR( 8000 )
	DECLARE	@tmpNum		INT
	DECLARE	@tmpNum2	INT
	
	DECLARE	@iNum		NUMERIC
	DECLARE	@Count		INT
	
	DECLARE	@iNum2		NUMERIC
	DECLARE	@Count2		INT
	


	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
			SET	Match	= @tmpCHAR
			WHERE	NR	= @iNum	
	
	
			SET	@Doing		= 'Get "FirstIndex" property.'
			EXEC	@hr		= sp_OAGetProperty @match, 'FirstIndex', @tmpNum OUTPUT
			IF @hr <> 0
				GOTO Error
	
			UPDATE	@Result
			SET	MIndex	= @tmpNum
			WHERE	NR	= @iNum
	
			SET	@Doing		= 'Get "Length" property.'
			EXEC	@hr		= sp_OAGetProperty @match, 'Length', @tmpNum2 OUTPUT
			IF @hr <> 0
				GOTO Error
			UPDATE	@Result
			SET	MLen	= @tmpNum2
			WHERE	NR	= @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
				SET	SubMatch1	= 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
				WHERE	NR	= @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 )
	SELECT		0, 
			'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
	')