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).
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).
posted by Mark Harr @ 12/07/2005 09:58:00 AM
3 Comments:
At 4/13/2006 1:37 PM ,
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 ,
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 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