Binaryworld - Regular Expressions in T-SQL ... [ SQL Server -> User Defined Function ]

For this solution we need SQL Server 2000 or higher. Also we need to make sure we have the VBScript.RegExp library on our computer. This should come with most Windows 2000 servers, in the Windows Scripting package. If you are using this on an older version of Windows, you will probably have to download the latest version of Windows Scripting for your OS.
The UDF
Here is the UDF that I wrote to search for a regular pattern expression in a source string:

Click here to copy the following block

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

Save this UDF into your database, and ensure that the permissions are set so it can be executed. Of course you will also need to ensure that people will have the permissions to execute the sp_OAxxxxx family of extended stored procedures for this to work.

This particular function has been used with no wrinkles and it seems to be a very snappy performer, even with the use of the COM object.

Example

One way to use regular expressions is to test for special characters. Instead of searching for all the special characters that exist, we’ll look for only matches of normal characters, like letters and spaces. Let’s see this in action:

Click here to copy the following block

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.find_regular_expression(
@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.find_regular_expression(
@vchSourceString2, @vchRegularExpression,0)

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

GO

The results for this example would be:

Click here to copy the following block

Test one This is a test!!
Special characters found.
---
Test two This is a test
No special characters.

Conclusion

As you can see, this is a very simple technique to get a very powerful result in certain situations. You as a T-SQL developer can take and extend this technique to other methods in the regular expression library VBScript.RegExp.