 By
Cory Koski
I recently had the problem of trying to search for a regular expression in a database
field. There is no version of SQL Server that supports regular expressions natively,
but I discovered a way to add all sorts of regular expression goodness to your
T-SQL applications. In order to use regular expressions easily, we can use a User
Defined Function to help us out and abstract the work for us.
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:
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:
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 ]{<number of characters of string>}
-- 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:
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.
First appeared at SQLTeam.com
About the Author:
Cory Koski is a full-time web developer living in Toronto, Ontario, for an international
travel insurance B2B company since June 2001. Born and raised in Thunder Bay,
Ontario, he has been working as a web developer building ASP-based solutions since
1997. In the last 4 years, Cory has worked extensively with SQL Server, and now
currently builds solutions with ASP.NET and SQL Server 2000. He is also a part-time
consultant and is also an academic speaker on the topic of web development practices.
Read this newsletter at: http://www.sqlpronews.com/2003/0707.html |
|
