Mark's Stuff

My Foray Into Weblogging. Using this to store interesting items for later review.

Wednesday, December 07, 2005

Ken Henderson's WebLog : Regular Expressions in T-SQL

Ken Henderson's WebLog : Regular Expressions in T-SQL

This was very handy for adding a TSQL function to validate email addresses during an import. This will only work with SQL Server 2000, and not 2005, but that is OK since 2005 can do regex in CLR functions anyways. Note that this is sp_oa methods, so it will not be appropriate to use this in triggers, or stored procedures that are performance-sensitive (this will be somewhat slow).

3 Comments:

  • At 4/13/2006 1:37 PM , Anonymous Anonymous said...

    Running on SQL2000 and utilizing SQL-DMO and regular expression as depicted in your article, it works great when running thru a small set of data!

    However, I'm finding somehow when feeding the string and regular expression parameters in a bulk process, SQL stops returning appropriate results after a certain record. I have attributed this to a memory issue, but have not found an answer to the problem. Any thoughts would help.

    Tru

     
  • At 4/14/2006 11:23 AM , Blogger Mark Harr said...

    Tru; Interesting problem you are having. I have used this for 10-20K rows before; although it was slow it did not have the problem your describe.

    Checking the original blog I link to here, there is a link to this discussion on how to get better performance. You might try that.
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205

     
  • At 5/02/2006 9:22 AM , Anonymous Anonymous said...

    Mark,
    Thanks for your input.
    What I found is that the COM object gets instantiated and destroyed too frequently when these set of regex codes are called from the WHERE clause embedded within a TSQL statement. This ultimately seem to cause SQL to mishandle memory. To workaround this, the creation and destruction of the COM object is done from an outer process. The resulted handle then gets passed into the regex function (UDF). In essence, it is instantiated once.

    Tru

     

Post a Comment

Subscribe to Post Comments [Atom]

<< Home