April 19, 2012 at 12:19 pm
how do we use Regular expressions in T-sql...Do we need to deploy the assemblies to sql server???
April 20, 2012 at 1:56 am
I'm sure that SQL Server does not support regular expressions within TSQL; I'd recommend you create a CLR.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 25, 2012 at 5:27 am
Robin is right, CLR is the way to go.
Example:
Regular Expressions Make Pattern Matching And Data Extraction Easier
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 26, 2012 at 8:43 am
What kinds of patterns you want to search for?
If not complex then you can use some expressions with LIKE clause.
Like if you want to search Canada ZIP codes, which are in following format 'X0X0X0', so for this you can use following query:
WHERE column_name LIKE '[a-zA-Z][0-9][a-zA-Z][0-9][a-zA-Z][0-9]'
Otherwise, like as other said, for complex searches you need CLR.
September 25, 2012 at 3:57 am
There are two T-SQL features which support limited regular expression functionality:
LIKE operator and PATINDEX function.
Depends of what you really need it for.
Note, the more complicated operation involved (eg. pattern match and replace), the length of the string and some other factors, then more likely that CLR solution will produce better performance. Also, CLR will allow to use RegEx objects with all available functionality.
September 27, 2012 at 11:29 pm
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.
-- Itzik Ben-Gan 2001
September 28, 2012 at 2:08 pm
Microsoft invested a huge amount of effort/time/money into CLR to give us tremendous capabilities. I have been told by someone close to the SQL dev team at MS that they are tremendously disappointed by the lack of adoption to the point they significantly curtailed planned efforts to make CLR even more robust. People like CELKO spread misinformation and outright lies about CLR to prevent more widespread adoption. Why they do such a thing is a guess, but I think it has something to do with not wanting to learn new things due to being stuck in the 80's (or 60's as the case may be.) This may seem harsh, but I think it's fantastically unwarranted to lie on a forum that's supposed to help younger developers get the truth about SQL Server.
September 28, 2012 at 5:10 pm
bteraberry (9/28/2012)
Microsoft invested a huge amount of effort/time/money into CLR to give us tremendous capabilities. I have been told by someone close to the SQL dev team at MS that they are tremendously disappointed by the lack of adoption to the point they significantly curtailed planned efforts to make CLR even more robust. People like CELKO spread misinformation and outright lies about CLR to prevent more widespread adoption. Why they do such a thing is a guess, but I think it has something to do with not wanting to learn new things due to being stuck in the 80's (or 60's as the case may be.) This may seem harsh, but I think it's fantastically unwarranted to lie on a forum that's supposed to help younger developers get the truth about SQL Server.
Well said.
I am by no means a Microsoft Koolaid drinker: Microsoft does some things I don't agree with but introducing CLRs was absolutely a great move on their part.
-- Itzik Ben-Gan 2001
September 28, 2012 at 9:04 pm
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 28, 2012 at 9:05 pm
XMLSQLNinja (9/28/2012)
bteraberry (9/28/2012)
Microsoft invested a huge amount of effort/time/money into CLR to give us tremendous capabilities. I have been told by someone close to the SQL dev team at MS that they are tremendously disappointed by the lack of adoption to the point they significantly curtailed planned efforts to make CLR even more robust. People like CELKO spread misinformation and outright lies about CLR to prevent more widespread adoption. Why they do such a thing is a guess, but I think it has something to do with not wanting to learn new things due to being stuck in the 80's (or 60's as the case may be.) This may seem harsh, but I think it's fantastically unwarranted to lie on a forum that's supposed to help younger developers get the truth about SQL Server.Well said.
I am by no means a Microsoft Koolaid drinker: Microsoft does some things I don't agree with but introducing CLRs was absolutely a great move on their part.
Ok... now you have me curious. What have you written in the form of a CLR?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2012 at 8:32 am
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
September 29, 2012 at 9:09 am
Jeff Moden (9/28/2012)
XMLSQLNinja (9/28/2012)
bteraberry (9/28/2012)
Microsoft invested a huge amount of effort/time/money into CLR to give us tremendous capabilities. I have been told by someone close to the SQL dev team at MS that they are tremendously disappointed by the lack of adoption to the point they significantly curtailed planned efforts to make CLR even more robust. People like CELKO spread misinformation and outright lies about CLR to prevent more widespread adoption. Why they do such a thing is a guess, but I think it has something to do with not wanting to learn new things due to being stuck in the 80's (or 60's as the case may be.) This may seem harsh, but I think it's fantastically unwarranted to lie on a forum that's supposed to help younger developers get the truth about SQL Server.Well said.
I am by no means a Microsoft Koo-laid drinker: Microsoft does some things I don't agree with but introducing CLRs was absolutely a great move on their part.
Ok... now you have me curious. What have you written in the form of a CLR?
There is no better way to start a Saturday morning than some CLR small talk. :w00t: If I could write a CLR that helps with a mild hangover that's what I'd be doing write now.
Over the years I have written CLR's for things like: applying regex statements to a string for the purposes of data cleansing/validation. I personally prefer XSLT transforms over SQLXML so I had to write a C# CLR that transforms XML. Stuff I can't do with T-SQL. This week I wrote an F# 'hello world' CLR (I am teaching myself F#).
Again, I aver that the introduction of CLRs was one of the best things Microsoft introduced in SQL 2005. Personally I avoid them whenever possible but not for the nonsensical reasons laid out by Mr. Celco.
-- Itzik Ben-Gan 2001
October 1, 2012 at 4:37 am
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.
Could anyone post a link, please.
October 1, 2012 at 6:02 am
Eugene Elutin (10/1/2012)
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.
Could anyone post a link, please.
I would also like to see a link to this thread.
-- Itzik Ben-Gan 2001
October 1, 2012 at 9:07 am
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.
To some extend I disagree.
CLR for SQL-Server is a kludge - a sensible kludge, because it provides extended functionality, albeit with a performance penalty incurred by the very nature of this kludge, at a much lower development cost for MS than providing it without the kludge (by a massive extension to the T-SQL language, with little or no prospect of getting the standards committees to support it for SQL, and probably several years of research and looking at design options before it could get under way) would have cost (and probably a much earlier release date too). But claiming that CLR never has a performance penalty, or that providing a CLR mechanism rather than putting a fully functional user-defined type/class capacity inside the data engine and in the T-SQL language would not be less of a kludge and deliver better performance for many things that currently have to be done with CLR is just crazy.
Of course there's nothing kludgy about using CLR to solve a problem that can't be done in T-SQL - the kludge is what MS did, not what its users are doing. Since MS has provided no other method of doing it, the users have no choice. Nor is there anything kludgy about using CLR for something that can be done in T-SQL in cases where CLR will deliver better performance (but be careful - some people have been quite surprised when they've done performance measurements).
Tom
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply