Learning Regular Expressions

7

September 2, 2015 by Kenneth Fisher

I’m a regular watcher of the Midnight DBA Webshow and one day they mentioned regular expressions. Not a new term for me, but not something I’d ever looked at much. Well as it happens I had something that I needed to use it on a day or two later and it worked like a charm. I was able to take a long comma delimited list of names and convert it to one name per line by converting the commas to carriage returns. I ended up mentioning it on Twitter and Sean McCown (b/t) (one of the Midnight DBAs in question) asked if I’d seen his session on the subject. I hadn’t. Yet. But I made a point of watching it this last weekend. I recommend spending the hour or so it takes. I learned a lot and the subject will be a big time saver over the years. In fact if I had to guess I probably would have saved most of that hour just in the last couple of weeks if I’d known it sooner.

Here’s a quick list of my favorite codes

  • ^ Beginning of a line
  • $ End of a line
  • \n Line break
  • \t Tab

 
\n and \t I knew before but ^ and $ are new from the session and I can’t wait to give them a try. All that being said here are the two most important things I’ve learned about regular expressions.

RegEx

Bonus!
I decided at the last minute I’d add my first real use of regular expressions.

Starting with a comma delimited list of about 50 names from outlook that looks like this:

Mabon, Sam <SMabon@MyCompany.com>; Kale, Matt <MKale@MyCompany.com>; Fisher, Kenneth <KFisher@MyCompany.com

First search and replace all “; “ with “\n”

Then search and replace all “{.+}, {.+}[ ]\” with “\2 \1”

And I get back:

Sam Mabon
Matt Kale
Kenneth Fisher

You can see how this could save quite a bit of time. I would be easy to put this back into a comma delimited list if you needed to by replacing “\n” with “, “.

7 thoughts on “Learning Regular Expressions

  1. notarian says:

    I saw Sean give that session at a SQL Saturday and I was already using this on Monday.

    • I’m looking forward to seeing Sean &/or Jen speak in person. And yea, I started using it more extensively the very next day. I’d used \n on a semi regular basis but not really anything more. It wasn’t until I watched the session that I really understood the scope. I mean just the ability to hit the end of every line is awesome.

  2. MidnightDBA says:

    I’m thrilled you guys are using this. It’s such an incredibly useful tool and I do something with regex every day, and that’s not an exaggeration. I can’t believe I used to work w/o them. Anyway, if you guys get stuck on something remember there are loads of regex libraries out there that have regex logic for almost anything you could ever want.
    Anyway, thanks for spreading the word about regex and about the session. It’s free anybody can go watch it.

  3. buckleygr says:

    Hi Kenneth,

    Welcome to the powerful world of regexes. It’s a journey to become skilful with them.

    I saw your regex and although it believe it works its syntax is so uncommon that it can’t be considered a dialect.
    I have maybe once tried to do a regex in VS or SSMS and saw that it has very little in common with what the community uses.
    Most regex engine are in the style of Perl with (mostly small) dialect differences.
    The Perl like regex engines included .NET, Java, C, etc.
    SSMS/VS is not part of that and I consider it a very bad start when learning regexes.

    My advice
    – use RegexBuddy. Really, its a must
    – The book “Mastering Regular Expression” from Friedl is excellent
    – The online reference regex.info is excellent as well (from the maker of RegexBuddy)

    Kr, Tom

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: