Learning Regular Expressions
7September 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.
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 “, “.
SQL Studies 
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.
Agreed. It’s those simple cmds that make all the difference.
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.
Yea, I’ve been using it more and more since writing this. All fairly basic stuff (except for the one that parses Outlook names) but still a real time saver.
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
I take a look, thanks 🙂 I did notice that regex in Word was different. I tend to use SSMS as my text editor 90% of the time so that’s where I started.