SQL Server 2005 - Using Regular Expressions

Hey,

How can I use regular expressions in a SELECT query?

this might not help

http://msdn.microsoft.com/msdnmag/is...x/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

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".

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.

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/archi...11/416392.aspx

Nice find. Can't wait to try it out with the SQLOLEDB.1 Provider and Ado.

Stan

Quote:

---

Originally Posted by stanl

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:

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:

Code:

---

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):

Code:

---

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:

Code:

---

Green
Greene

---

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

[vba]
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
[/vba]

to remove the function
[vba]
oCmd.CommandText = "DROP FUNCTION [dbo].[fn_regex]"

[/vba]

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...

[VBA]exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT
[/VBA]

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