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 ]{}
-- 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.
----------------------
CREATE FUNCTION dbo.regexObj
(
@regexp varchar(1000),
@globalReplace bit = 0,
@ignoreCase bit = 0
)
RETURNS integer AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 BEGIN
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 BEGIN
RETURN NULL
END
RETURN @objRegExp
END
GO
CREATE FUNCTION dbo.regexObjFind
(
@objRegExp integer,
@source varchar(5000)
)
RETURNS bit AS
BEGIN
DECLARE @hr integer
DECLARE @results bit
EXECUTE @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
RETURN NULL
END
RETURN @results
END
GO
CREATE FUNCTION dbo.regexObjReplace
(
@objRegExp integer,
@source varchar(5000),
@replace varchar(1000)
)
RETURNS varchar(1000) AS
BEGIN
DECLARE @hr integer
DECLARE @result varchar(5000)
EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 BEGIN
RETURN NULL
END
RETURN @result
END
GO
CREATE FUNCTION dbo.regexFind
(
@source varchar(5000),
@regexp varchar(1000),
@ignoreCase bit = 0
)
RETURNS bit AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @results bit
SET @results = 0
EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
RETURN NULL
END
RETURN @results
END
GO
CREATE FUNCTION dbo.regexReplace
(
@source varchar(5000),
@regexp varchar(1000),
@replace varchar(1000),
@globalReplace bit = 0,
@ignoreCase bit = 0
)
RETURNS varchar(1000) AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @result varchar(5000)
EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
RETURN NULL
END
RETURN @result
END
GO
------------------------------------------
- remove anything other then a-z from firstname field
SELECT dbo.regexReplace( firstname, '[^a-z]', '', 1, 1 ) FROM account;
-- return only account records which have any characters other then a-z
SELECT * FROM account WHERE dbo.regexFind( firstname, '[^a-z]', 1 ) = 1;
--------------------------------------------------------------------------------
The longer, and more efficient version of the 2 statements above are:
-- remove anything other then a-z from firstname field
DECLARE @regex integer;
SET @regex = dbo.regexObj( '[^a-z]', 1, 1 );
SELECT dbo.regexObjReplace( @regex, firstname, '' ) FROM account;
-- return only account records which have any characters other then a-z
DECLARE @regex integer;
SET @regex = dbo.regexObj( '[^a-z]', 0, 1 );
SELECT * FROM account WHERE dbo.regexObjFind( @regex, firstname ) = 1;
Of course the power of regex can do WAY more then I've illustrated above, being able to update values in the database with regex, do very complex regex search filtering, and more.
浙公网安备 33010602011771号