A short technical post on one thing I’ve found annoying.

Anyone who has worked with computers in the last decade has probably used regular expressions in some form or another, or “regexs” as they’re known as. First (as far as I know) popularized in Perl (though their history stretches back to the 1950s), they’ve become standard fair in most languages and tools and are very useful for complex matching and find and replaces.  And for anyone who has worked with computers for over two decades they often look like line noise when someone would pick up the phone in the house when you were dialed in via an actual modem.

That said, I first learned about their value in SQL Server Management Studio due to a great talk by Sean McCown. (note the post’s byline is Jen’s, but trust me, I saw Sean give the talk. 😉

One of the powerful features is the ability to “tag” part of an expression so you can use it in your replace statement (see the above link for more details.)

But, here’s the thing, somewhere along the line (I think it was post SSMS 2014) Microsoft changed the rules of the game and it can be hard to find the new rules!  They have a post on using regexp in SSMS 2017. But as far as I can tell, it’s the old 2014 info, simply rebranded. Some of it, especially the tagging part does not appear to work for me. If anyone CAN make it work in 2017, please let me know how.

Let me give you an example. Just today I was given a script that had a lot of statements similar to:

DROP TABLE If Exists ##TempAttribute

Let me say I LOVE the new “If Exists” option for DROP Table, but, I needed to run this on SQL Server 2008R2 (I know, don’t ask!) and that syntax won’t work.

I needed to replace it with something like:

IF OBJECT_ID('##TempAttribute', 'U') IS NOT NULL
  DROP TABLE ##TempAttribute; 

Now, I’m naturally lazy and didn’t want to have to find and replace all 100 or so instances of this by hand. So, I reached for regexes… and… well it didn’t go well.

Based on the old syntax my find should look something like for the find:

DROP Table if exists {\#\#[A-z_0-9]+}

And for the replace

if object_ID('\1', 'U') is not null drop table \1;

Except, for the life of me, that wasn’t working. Every time I tried to tag the table name using the braces {} my regex would fall apart.  So of course I searched and got the link from Microsoft above that still suggests tagging with braces.  From previous experience I knew it was wrong, but that IS the official Microsoft page after all, so I kept doubting myself.

But, I was right in remembering things had changed.

The proper syntax is:

Drop table if exists (\#\#[A-z_0-9]+)

and

if object_ID('$1', 'U') is not null drop table $1;

The change to the search expression is subtle, changes braces to curved parenthesis .

The change to the replace is about the same, changing a \ to a $ but I suspect (I have not confirmed) that you’re no longer limited to just 9 tagged expressions.

And before anyone chimes in, I do realize there are some other ways of writing the search expression (such as I could have used :w+ instead in SSMS 2014 that would have worked in my particular case, since there were no temp tables with numbers, but this would not have worked in SSMS 2017), but this worked for me and wasn’t the point of this post. My goal was to focus on the change in tagging expressions.

Regular Expressions are still one of those things that don’t come very easily to me so I often struggle with the syntax and it doesn’t help when Microsoft changes the syntax rules between versions of SSMS (my understand they did so to make SSMS functionality better match Visual Studio, so I’m ok with this), but overall, I find them EXTREMELY useful and if you haven’t played with them, I highly recommend you start learning. There can be a bit of a learning curve for anything too complex, but it’s worth it.

My advice, start with learning how to “grab” the beginning and the “end” of a line and then go from there. This is the most useful thing to me at times when I want to add or remove something at the start or end of every line.

Happy expressing!