Jeff Moden (9/28/2012)
XMLSQLNinja (9/27/2012)
CELKO (9/15/2012)
...And then there is a high cost of CLR. .Hogwash. There is nothing "high cost" about a well written CLR.
Avoid this kludge if you can.
There's nothing "kludgy" about using a CLR to solve a problem that can't be solved by using T-SQL. Period.
I concur with the previous posters who suggest using a CLR for regular expressions.
As with all else, "It Depends". In the case of RegEx CLR vs Like and PatIndex... if you can actually work it out with Like, PatIndex, or even CharIndex, it will usually be faster than making a call to a RegEx CLR. A couple of us just went through all of this on another thread a couple of months ago and the LIKE expressions blew the doors off the RegEx CLR.
Good morning Mr. Moden and Happy Saturday!
If you recall our discussion a few months back about running totals, I do not advocate implementing a CLR for something you can accomplish with a T-SQL query (unless, perhaps there were big performance benefits.) Furthermore, I concur that, if you can work it out with LIKE, CHARINDEX, etc then that is the way to go. Using manub22's Canadian Zip code example: writing some C#, VB, etc, then compiling it, creating a new CLR assembly, adding new managed code to your environment and Source Control, would be, as an old co-worker used to say, "like pole vaulting over a mole hill."
A couple of us just went through all of this on another thread a couple of months ago and the LIKE expressions blew the doors off the RegEx CLR.
I hope you (or anyone else) doesn't think I was claiming that there would be performance benefits by creating a Regex CLR for something that could be resolved with a LIKE expression.
So we have some context - my point was: for things like validating an Email address I cannot think of a better to do this than with a regex CLR.
My other point was that I agreed with bteraberry's post.
-- Itzik Ben-Gan 2001