PDA

View Full Version : SQL Server 2005 - Using Regular Expressions



malik641
09-12-2007, 07:05 AM
Hey,

How can I use regular expressions in a SELECT query?

stanl
09-12-2007, 08:40 AM
this might not help

http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx

it is in .net, not VBA; of course you can always use regex in functions called as part of SQL, i.e.

SELECT name, email from [dbo].[clients] WHERE validmail(email)=1;

.02 Stan

Tommy
09-12-2007, 10:39 AM
That was a good article Stan.

Joseph the Like keyword is a great tool to use. It has most? of the power but not as much. :) Syntax is about the same. Since we are dealing with "text".

malik641
09-12-2007, 12:47 PM
If you ask me Like is like the wannabe of regular expressions :) But that's just me.

I am reading the article (periodically) now. Thanks for the link Stan.

malik641
09-14-2007, 01:13 PM
Haven't finished the first posted article, but I found this today. It's worth reading if you're developing anything in SQL Server :)

http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx

stanl
09-14-2007, 01:56 PM
Nice find. Can't wait to try it out with the SQLOLEDB.1 Provider and Ado.

Stan

malik641
09-14-2007, 02:16 PM
Nice find. Can't wait to try it out with the SQLOLEDB.1 Provider and Ado.

Stan
I know! I'm excited too! :) This will help me LOADS at my job :joy:

malik641
09-16-2007, 07:17 AM
Awesome :)

I got the function working in SQL Server Express edition. I had to Enable OLE Automation by going to SQL Server 2005 Surface Area Configuration --> Surface Area Configuration for Features --> OLE Automation --> Enable OLE Automation.

I'm not familiar with Stored Procedures yet, so I'm not sure if I can define a global function and use it in any database that I create a query on. For now, I define the function in each database separately. Here's the T-SQL code I am using:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION
[dbo].[fn_regex](@pattern varchar(255), @matchstring varchar(8000))
RETURNS int

AS

BEGIN
declare @obj int
declare @res int
declare @match bit
set @match=0

exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT

IF (@res <> 0) BEGIN
RETURN NULL
END

exec @res=sp_OASetProperty @obj, 'Pattern', @pattern

IF (@res <> 0) BEGIN
RETURN NULL
END

--exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1

--Changed - 9/15/2007
exec @res=sp_OASetProperty @obj, 'IgnoreCase', 0

IF (@res <> 0) BEGIN
RETURN NULL
END

exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring

IF (@res <> 0) BEGIN

RETURN NULL

END

exec @res=sp_OADestroy @obj
return @match
END I changed the "Ignore Case" to FALSE because I would like to have my regEx queries case-sensitive. I guess we could always change the function's parameters to include an optional boolean on whether or not we want case sensitivity turned on...

If anyone is unfamiliar on how to implement this code, the way that I did it was the following (and I'm sure there are other ways to do this):
In Microsoft SQL Server Management Studio Express edition, navigate to your database through the Object Explorer. Then go to Programmability --> Functions --> Scalar-valued Functions --> Right-click and select "New Scalared Valued function..." then copy and paste the above code in there and execute it. After you've run the code once, the "CREATE FUNCTION" will automatically change to "ALTER FUNCTION" and the function's code will be stored in the "Scalar-valued Functions" folder (to view it, click the Refresh button in the Object Explorer).

An example of this function in action (using the Pubs sample database):

SELECT au_lname
FROM authors
WHERE dbo.fn_regex('^[gG].e',au_lname)<>0;
Looks for an Author's last name starting with a lower-case "g" or an upper-case "G", then any Single character following (like Gl, Gr, etc) then the third character has to be a lower-case "e", and anything after that. The results returned:

Green
Greene

stanl
09-16-2007, 10:47 AM
This also works well with the OLEDB Provider and functions can be created and dropped programatically. If you leave off "Initial Catalog=" in the connection string it will default to the scaler functions under the Master Database, otherwise it will under the particular Catalog [or database]. If you leave off the [dbo]. it should be automagically appended. I assume the scope is to the catalog; I'm not sure you can use [sys] since is not a schema prefix.

The code below leaves off initial catalog and assumes the function was not previously created. Stan


sub sqlcrfunc
cText="CREATE FUNCTION" & vbcrlf
cText=cText & "fn_regex(@pattern varchar(255), @matchstring varchar(8000))" & vbcrlf
cText=cText & "RETURNS int" & vbcrlf
cText=cText & "AS" & vbcrlf
cText=cText & "BEGIN" & vbcrlf
cText=cText & " declare @obj int" & vbcrlf
cText=cText & " declare @res int" & vbcrlf
cText=cText & " declare @match bit" & vbcrlf
cText=cText & " set @match=0" & vbcrlf
cText=cText & " exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT" & vbcrlf
cText=cText & " IF (@res <> 0) BEGIN" & vbcrlf
cText=cText & " RETURN NULL" & vbcrlf
cText=cText & " END" & vbcrlf
cText=cText & " exec @res=sp_OASetProperty @obj, 'Pattern', @pattern" & vbcrlf
cText=cText & " IF (@res <> 0) BEGIN" & vbcrlf
cText=cText & " RETURN NULL" & vbcrlf
cText=cText & " END" & vbcrlf
cText=cText & " exec @res=sp_OASetProperty @obj, 'IgnoreCase', 0" & vbcrlf
cText=cText & " IF (@res <> 0) BEGIN" & vbcrlf
cText=cText & " RETURN NULL" & vbcrlf
cText=cText & " END" & vbcrlf
cText=cText & " exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring" & vbcrlf
cText=cText & " IF (@res <> 0) BEGIN" & vbcrlf
cText=cText & " RETURN NULL" & vbcrlf
cText=cText & " END" & vbcrlf
cText=cText & " exec @res=sp_OADestroy @obj" & vbcrlf
cText=cText & " return @match" & vbcrlf
cText=cText & "END" & vbcrlf
adCmdUnknown = 8
cConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS"
Set oConn = CreateObject("ADODB.Connection")
Set oCmd = CreateObject("ADODB.Command")
oConn.CommandTimeOut=0
oConn.Open cConn
oCmd.ActiveConnection = oConn
oCmd.CommandType = adCmdUnknown
oCmd.CommandText = cText
oRS = oCmd.Execute
Set oCmd= Nothing
oConn.Close
Set oConn=Nothing
End sub


to remove the function

oCmd.CommandText = "DROP FUNCTION [dbo].[fn_regex]"

stanl
09-18-2007, 01:02 PM
As an afterthought. Assume you are doing a SELECT query using the regex function on a table with 100,000 rows - and let's say 4000 rows meet the regex criteria. The function appears to use a late-binding call to the Regex Activex...

exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT


does this mean your run a stored proc CreateObject() 100,000 times and I wonder what the performance hit would be. Or maybe it doesn't work like that... just curious:think: Stan