Regular expression in T-SQL
Author: Cory Koski Published: 2003/06/24
This article comes from Cory Koski. Cory wrote: "I have recently encountered a problem, just trying to search for a regular expression in the database domain. There is no version of SQLServer to support regular expressions, but I found all the advantages of regular expressions to all the advantages added to Your T_SQL application method. To make it easier to use regular expressions, we can use custom functions (User Defined Function, UDF) to help us and make your work simple. "
In this solution, we need SQL Server 2000 or higher. We also need to make sure there are VBScript.Regexp class libraries in the machine, which has the Windows scripting package in most Windows 2000 Servers. If you are using an earlier version of Windows, you must download the latest version of Windows scripting for your operating system. Custom Functions The following is my custom function, which can be used to search for a regular mode expression in the source string. CREATE FUNCTION dbo.find_regular_expression (@source varchar (5000), @regexp varchar (1000), @ignorecase bit = 0) RETURNS bitAS 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 to your database and determine authorization to run it. Of course, you have to make sure that people running it have permissions that run the sp_oaxxxxx class extension stored procedure. This function has ensured that it is running normally, and even if it is used with the COM object, it is still very fast.
For example, a place to use the regular expression is to test a special character. We don't search all special characters, but look for normal characters, such as letters and spaces. Let's take a look at its operation. DECLARE @intLength AS INTEGERDECLARE @vchRegularExpression AS VARCHAR (50) DECLARE @vchSourceString as VARCHAR (50) DECLARE @ vchSourceString2 as VARCHAR (50) DECLARE @bitHasNoSpecialCharacters as BIT-- initializes the variable SET @vchSourceString = 'Test one This is a test !! 'Set @ vchsourceString2 =' Test Two this is a test '
- Our regular expression should be similar to - [A-ZA-Z] {} - such as: [A-ZA-Z] {10} ... a string of characters
- Get strings length set @intlength = len (@vchsourceString)
- Set the full regular expression set @vchregulaarExpression = '[A-ZA-Z] {' CAST (@intlength as varchar) '}'
- Is there any special character set @bithasnospecialcharacters = dbo.find_regular_expression (@vchsourceString, @ vchregulaearxpression, 0)
Print @vchsourceStringif @bithasnospecialcharacters = 1 Begin Print 'No Special Characters.'ENED Else Begin Print' Special Characters Found.'END
PRINT '---'
- Get strings length set @intlength = len (@ vchsourceString2)
- Set the full regular expression set @vchregulaarExpression = '[A-ZA-Z] {' CAST (@intlength as varchar) '}'
- Is there any special character set @bithasnospecialcharacters = dbo.find_regular_expression (@ vchsourceString2, @ vchregularexpression, 0)
Print @ vchsourceString2if @bithasnospecialcharacters = 1 Begin Print 'no special character character, de ,,,,,,,,,,,,,,,,
Gothe Results for this Example Would Be: The result of this example should be: Test One this is a test !! Special Characters found .-- Test Two this is a testno special character.
