Regular expressions in T-SQL

Regular

Regular expressions in
T-SQL








Original Author: Cory Koski


Release date: 2003/06/24








This article comes from Cory Koski. Cory wrote: "I recently encountered a problem of trying to search a regular expression in a database domain." There is not a single version of SQL Server that supports regular expressions, but I have found a way to add all the benefits of a regular expression to your T_sql application. To make it easier to use regular expressions, we can use custom functions (User Defined function, UDF) to help us and keep our work simple. ”





in this solution, we need SQL Server 2000 or higher. We also need to make sure that there are vbscript.regexp class libraries in the machine, which are included with the Windows Scripting package in most Windows servers. If you are using an earlier version of Windows, you must download the latest version of Windows scripting for your operating system.


Custom Function


below is my custom function, which can be used to search a regular pattern expression in the 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 to your database and make sure you have authorization to run it. Of course, you also have to make sure that the person running it has permission to run the Sp_oaxxxxx class extended stored procedure.


This function is guaranteed to work correctly and is very fast even when used with COM objects.





examples

One place where
use regular expressions is to test special characters. Instead of searching for all the special characters, we look for matches for normal characters, such as letters and spaces. Let's see how it works.


DECLARE @intLength as INTEGER


DECLARE @vchRegularExpression as VARCHAR (50)


DECLARE @vchSourceString as VARCHAR (50)


DECLARE @vchSourceString2 as VARCHAR (50)


DECLARE @bitHasNoSpecialCharacters as BIT





--Initialization variable


SET @vchSourceString = ' Test One ' is a test!! '


SET @vchSourceString2 = ' Test Two This is a test '





--Our regular expression should be similar to the


--[A-za-z]{}


--such as: [A-za-z]{10} ... String
of a cross character




--Get string length


SET @intLength = LEN (@vchSourceString)





--Sets the full regular expression


SET @vchRegularExpression = ' [a-za-z]{' +


CAST (@intLength as varchar) + '} '





--Do you have 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 string length


SET @intLength = LEN (@vchSourceString2)





--Sets the full regular expression


SET @vchRegularExpression = ' [a-za-z]{' +


CAST (@intLength as varchar) + '} '





--Do you have 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 is:


The result of this example should be:


Test One is a test!!


Special characters found.


---


Test Two This is a test


No special characters.





Conclusion:


as you can see, this is a simple technique that gets very useful results on a particular occasion. As a t_sql developer, you can use and extend this technique in the regular expression library vbscript.regexp.