Solving development problems  |  About this blog

We have found nice explanation how you can upgrade Transact-SQL to work with Regular Expressions

First, you need to create this SQL functions for regular expressions:

CREATE FUNCTION [dbo].[FindRegularExpression]
	(
		@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

This is an example to test it:

DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(50)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @vchSourceString2 as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

-- Initialize variables
SET @vchSourceString = 'Test one This is a test!!'
SET @vchSourceString2 = 'Test two This is a test'

-- Our regular expression should read as:
-- [a-zA-Z ]{}
-- eg. [a-zA-Z ]{10}  ...  For a string of 10 characters

-- Get the length of the string
SET @intLength = LEN(@vchSourceString)

-- Set the completed regular expression
SET @vchRegularExpression = '[a-zA-Z ]{' + CAST(@intLength as varchar) + '}'

-- get whether or not there are any special characters
SET @bitHasNoSpecialCharacters = dbo.FindRegularExpression(
   @vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString
IF @bitHasNoSpecialCharacters = 1 BEGIN
	PRINT 'No special characters.'
END ELSE BEGIN
	PRINT 'Special characters found.'
END

PRINT '---'

-- Get the length of the string
SET @intLength = LEN(@vchSourceString2)

-- Set the completed regular expression
SET @vchRegularExpression = '[a-zA-Z ]{' + CAST(@intLength as varchar) + '}'

-- get whether or not there are any special characters
SET @bitHasNoSpecialCharacters = dbo.FindRegularExpression(
   @vchSourceString2, @vchRegularExpression,0)

PRINT @vchSourceString2
IF @bitHasNoSpecialCharacters = 1 BEGIN
	PRINT 'No special characters.'
END ELSE BEGIN
	PRINT 'Special characters found.'
END

GO

If you got an error message you need to activate SQL server property with the code bellow this message:

Msg 15281, Level 16, State 1, Line 22
SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online.
Test one This is a test!!
Special characters found.
—
Msg 15281, Level 16, State 1, Line 40
SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online.
Test two This is a test
Special characters found.

In order to get rid or red text (error) do this to enable this function (sp_OACreate ‘VBScript.RegExp’) :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO