June 8, 2006 at 3:32 am
I have written a web tool for our admin guy that helps him set up one of our sites, it allows him to customise certain bits of text, messages, emails that are used in the system. I have created a Find function where he can enter a word to search against the DB, but he also has two other options one to find phone no's and the other to find email addresses. A lot of the time when we replicate a site the original sites email addresses and phone nos get copied into the new site so he needs a quick way to find them all to change them.
I started going down the Like / Patindex method but the limitation of 4 wildcards prevented me from writing a good match criteria so I nicked some code from Ken Henderson for a udf that uses sp_OACreate to hook into the VBScript COM component so I can do proper reg expressions.
However as the create/destroy calls were in the udf and I was calling it sometimes hundreds/thousands of times I thought of taking the create/destroy calls out and putting them in the calling stored proc and just passing the reference to the object into the udf.
As I have not used sp_OACreate before what I want to know is that is passing the integer reference into the udf the best way of accomplishing what I want rather than having the create/destroy contained in the udf. Are there any other implications that I am not aware of?
Here is the code.
--This code will be in a stored proc
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @results bit
EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
--reg exp will change depending on what is being searched for
declare @regexp varchar(255)
set @regexp = '[a-z0-9_\.\-\'']+@[a-z0-9\.\-]+\.[a-z]{2,4}'
SELECT d.SentanceID as ID, d.Sentance as cDefault, c.Sentance as cCustom
FROM tbl_DEFAULT_SENTENCES as d
LEFT JOIN tbl_CUSTOM_SENTENCES as c
ON d.PageFK = c.PageFK AND
d.SentanceID = c.SentanceID AND
c.SiteFK = 18
WHERE dbo.udf_REG_EXP_2(@objRegExp,d.Sentance,@regexp,1)=1 OR
dbo.udf_REG_EXP_2(@objRegExp,c.Sentance,@regexp,1)=1
ORDER BY d.SentanceID;
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
--this is the udf
CREATE FUNCTION dbo.udf_REG_EXP_2
(
@objRegExp int,
@source varchar(2000),
@regexp varchar(1000),
@ignorecase bit = 0
)
RETURNS bit
AS
BEGIN
DECLARE @hr integer
DECLARE @objMatches integer
DECLARE @objMatch integer
DECLARE @count integer
DECLARE @results bit
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0
BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
June 8, 2006 at 6:12 am
I have done the same thing but with a recursive SP instead of a UDF. Passing the reference worked (and is still working in a heavy production environment) perfectly.
You are aware of OLE limitations I suppose? (the 256 max instances). By reusing the object and disposing asap, I nerver hit it but I have always been a bit nervous about it.
With SQL2k5 you can build a VB/C# RegEx UDF that is much simpler and is orders of magnitudes faster. I created one* to help me build complex CHECK contraints. Fun and extremely efficient even with enourmous IO. You can even precompile your RegEx expression if you want to.
* there are examples in many places but I found a nice one in the MSDN blogs (blogs.msdn.com). Search on SQL and RegEx (possibly RegExMatch) and you should find it.
June 8, 2006 at 6:41 am
I know you may need rich or adaptable regexes, but if it's just for checking email addresses, you should at least bear in mind that you can also use LIKE which is reasonably expressive, if unwieldy. The best way to use it is by applying multiple LIKE conditions (often negative ones) to narrow down the pattern you want. For example, here's some simple code for validating email addresses - with the rules (invented for test purposes) it implements alongside. From:http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=284040#bm284262
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 8, 2006 at 7:41 am
Ok cheers for response. The 256 instance issue should not be a problem as its only going to be our admin guy using it at any one time. If the system was on a 2005 server I would have written the proc in c# but alas we are on 2k.
The multi like statement is good for looking at one column/var that is only supposed to contain an email address. But the code I have is looking at tables that could contain whole sentances so if the sentance ends in a full stop. Or there is more than 2 email addresses in there it wouldn't work. I'm sure if I had the time I could come up with a multi like statement that could work around that problem but at the moment I don't so I will go with the Reg Expr version until I find one.
Thanks for comments.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply