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 “, “.

Filed under: SQLServerPedia Syndication, SSMS Tagged: Regular Expressions, SSMS