Consulting

Results 1 to 10 of 10

Thread: SQL Server 2005 - Using Regular Expressions

  1. #1
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location

    SQL Server 2005 - Using Regular Expressions

    Hey,

    How can I use regular expressions in a SELECT query?




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  2. #2
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  3. #3
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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".

  4. #4
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    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.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  5. #5
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    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




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  6. #6
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Nice find. Can't wait to try it out with the SQLOLEDB.1 Provider and Ado.

    Stan

  7. #7
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    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




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  8. #8
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    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




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  9. #9
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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]

  10. #10
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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 Stan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •