DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets Manager

Snippets has posted 5883 posts at DZone. View Full User Profile

SQL Server 2000 RegEx

03.25.2006

| 6417 views |

        // SQL 2K RegEx Compat Code

CREATE FUNCTION dbo.find_regular_expression
	(
		@source varchar(5000),
		@regexp varchar(1000),
		@ignorecase bit = 0
	)
RETURNS bit
AS
	BEGIN
		DECLARE @hr integer
		DECLARE @objRegExp integer
		DECLARE @objMatches integer
		DECLARE @objMatch integer
		DECLARE @count integer
		DECLARE @results bit
		
		EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
		EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
		EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
		EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
			
		EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
		EXEC @hr = sp_OADestroy @objRegExp
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
	RETURN @results
	END

Tags: