<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by David Poole
»
RegEx for DBAs
48 posts, Page 1 of 5
1
2
3
4
5
»
»»
RegEx for DBAs
Rate Topic
Display Mode
Topic Options
Author
Message
David.Poole
David.Poole
Posted Monday, May 07, 2012 9:06 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:13 PM
Points: 2,766,
Visits: 1,442
Comments posted to this topic are about the item
RegEx for DBAs
LinkedIn Profile
Post #1296195
rraja
rraja
Posted Monday, May 07, 2012 9:08 PM
Valued Member
Group: General Forum Members
Last Login: Monday, February 18, 2013 6:53 AM
Points: 60,
Visits: 122
Very nice Article.
Post #1296197
david.rowland
david.rowland
Posted Monday, May 07, 2012 9:11 PM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:44 PM
Points: 26,
Visits: 86
Although RegEx is powerful, I have tried using it once and found that coding my own CLR function to manipulate strings to what I want was 100 times faster than the RegEx function was.
If you are running the code against a small dataset this should not matter however I was running the code against millions of rows and this had a major impact on performance.
Post #1296199
Jeff Moden
Jeff Moden
Posted Tuesday, May 08, 2012 12:19 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:05 PM
Points: 33,112,
Visits: 27,039
Great and very nicely put together article, David. My hat is off to you. But what of performance? Have you done any comparisons between the Regex function and a moderately complex PatIndex?
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1296241
Jeff Moden
Jeff Moden
Posted Tuesday, May 08, 2012 12:21 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:05 PM
Points: 33,112,
Visits: 27,039
david.rowland (5/7/2012)
Although RegEx is powerful, I have tried using it once and found that coding my own CLR function to manipulate strings to what I want was 100 times faster than the RegEx function was.
If you are running the code against a small dataset this should not matter however I was running the code against millions of rows and this had a major impact on performance.
What did your custom CLR do to beat Regex so soundly?
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1296243
corey lawson
corey lawson
Posted Tuesday, May 08, 2012 1:02 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 135,
Visits: 220
I've used our own in-house implementations of some of the .Net RexEx functions on seemingly large datasets (several million records each), mostly to help clean up, validate and parse addresses, city-state-zip (or province), etc.
Beats pure SQL functions like Hulk did to Loki in the movie, especially in understandability. I'll take teasing a complicated Rexex pattern over scads of nested SQL functions trying to do the same thing.
one I really have done lots of is to do is keep expanding on a pattern like this: '(Company|corp|corporation|inc|llp|llc...)'
beats continuing to add "or field like '%new_match%' ..."
Post #1296251
borisk 57245
borisk 57245
Posted Tuesday, May 08, 2012 2:22 AM
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 3:39 AM
Points: 3,
Visits: 36
RegEx is Thee best tool in order to find any pattern within given string[did couple of test including .Net String manipulation and limited RegEx in SQL , Like/PATIndex....], and it proves to be much faster than any other way.
in regards to good editor, try this one: http://www.ultrapico.com/Expresso.htm
moreover, consider using FullText indexing from time to time, as it might even ease searching for Words other than using RegEx.
Post #1296283
Jeffrey Bijkerk
Jeffrey Bijkerk
Posted Tuesday, May 08, 2012 4:20 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 1:22 AM
Points: 2,
Visits: 34
If you use regex combined with a where clause it can not use the index anymore. I think this is very important.
Post #1296347
williams.frd
williams.frd
Posted Tuesday, May 08, 2012 4:39 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, January 22, 2013 8:13 AM
Points: 1,
Visits: 12
Am I out of line in thinking this is something that should be handled in a high-level object-oriented language?
Post #1296354
paul.knibbs
paul.knibbs
Posted Tuesday, May 08, 2012 6:48 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,272,
Visits: 4,309
I'm not particularly familiar with the CLR--does processing stuff via RegEx in this way effectively mean you're using RBAR, or is it a bit cleverer than that?
Post #1296415
« Prev Topic
|
Next Topic »
48 posts, Page 1 of 5
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
