Monthly Archives: February 2019

SSMS 2017 RegEx

Posted on by
Reply

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!

Wear their shoes

Posted on by
Reply

Last night I had the pleasure of hosting our local SQL Server User Group and having Rie Irish speak. Despite it being a remote presentation, which usually draws a smaller crowd, we had one of our larger crowds in awhile; I was quite pleased.

The topic was “Well Actually… Don’t be THAT guy in IT.” I first saw her present this at the Atlanta SQL Saturday 2018 and knew I wanted her to speak again to my user group.  She had previously presented “Let Her Finish” and this was a good follow-up.

One of the points she makes during this particular talk is that men don’t know what it’s like to be in a woman’s shoes.  This triggered a memory of when I was a wee lad.

Let’s jump back into the old Time Machine and dial it back to early September 1985. It’s evening on the campus at RPI and a young college freshman is hanging out with one or two other guys trying to figure out what evening session for their student orientation they should go to. They see a session provided by the Society of Women Engineers (SWE) talking about women in engineering. One of them says, “Oh that should be fun” in a voice that probably had at least a bit of snark in it. So, this young, intrepid and naive freshman follows along, after all, at a school with a 5:1 ratio, one might as well go where there’s some women.

Now, this young freshman can’t quite remember the entire session, but he did come away with a very different impression than he thought he would. Basically it was an “Oh wow” moment. Prior to that he had no idea the sexism women might face at an engineering college where in theory one was admitted solely on merit. It was eye-opening.

Jumping back into the time machine, we can dial it forward about 18 months and set down at a house off campus where he’s talking with one of his housemates. She mentions she had started as an architecture major but changed majors, for a variety of reasons. But, one thing that stood out was her first day of class where a professor made it quite clear that he didn’t think women should be architecture majors. He was a bit shocked that such attitudes still existed, but by then wasn’t entirely surprised.

Again, jumping in the time machine, he dials things forward about 2 years later. He’s sitting in the backyard of a sorority house talking with his girlfriend and a mutual friend. The mutual friend is a geology major. She mentions how she has gotten into the habit of submitting her homework and papers with simply her first initial and last name. This less naive man doesn’t quite get the reason why at first until she points out that this way, her professor can’t as easily identify her gender, and it makes her life easier and she tends to get better grades that way. He doesn’t want to believe it, but he does, because he figures she has no reason to lie.  It angers him though that she has to do it.

Let’s jump forward now about two decades. Due to his then current work situation, he’s actually staying with the now former girlfriend from college. In the years since they graduated and broke up, she had gone on to become a VP of engineer at a medical devices company before eventually quitting and going into consulting. He’s making dinner when she comes home from a meeting she had with a client. She’s visibly upset.

He asks why. After all, she was simply going there to give her final report on an item she had been asked to review and to get paid for that report.  The client had accepted the paper, and then asked her for a date. What had been a professional setting now became an awkward setting where she was placed in a position of having to say no to something she never expected to come up and to still make sure she got paid.

Jumping into the time machine one last time, we return to the modern day where Rie is still speaking. This no longer young man has to agree.

He’s had glimpses into what close friends have gone through, but, that’s exactly what they are. Glimpses. He didn’t experience them. He has never, as another friend has had happen, been told if he gave a blow job, he would get the job.  He’s never had a door close behind him and a manager awkwardly try to make a move on him.  He hasn’t woken up most mornings wondering, “who will question my credentials today because I have large breasts.”

Today’s takeaway for a number of my readers is: listen to your colleagues and believe their experiences, but don’t for a minute claim to fully understand them. Many of us never can and never will.

Oh, and one more comment: this author is far from perfect when it comes to handling gender and other similar issues. It’s an ongoing process.  I’m still trying to learn and grow.

 

Use the steel carabiner!

Posted on by
Reply

“It’s stronger.”

As I’ve mentioned I’m an instructor with the National Cave Rescue Commission. During our classes we teach a variety of skills using a variety of equipment. Among the equipment we use are carabiners of various sizes and materials. The two most common materials are aluminum and steel.  Each has its advantages and disadvantages.

Aluminum is almost always lighter and this can be a real advantage when you have to carry a lot of them.

An aluminum carabiner may have a MBS (mean breaking strength) of 20kN (kiloNewtons or about 4,500lbs) along its long axis. (Different designs and different manufacturers will have different values, and orientation can make a huge difference).

A steel carabiner may have an MBS of 25kN along the same axis. So, it’s obviously stronger.

But here’s the thing. When we’re moving a patient, we have to look at the entire system.  In the NCRC we call this the system safety ratio (SSR).  You can’t look at just one component. Imagine using a carabiner (steel or aluminum) and trying to haul a patient with dental floss. It doesn’t matter what carabiner you use, that dental floss won’t get you very far.

And honestly, if you’re at the point where the strength of the carabiner is that critical such that the difference between 20kN and 25kN is critical, I would recommend you review your entire system.  There might be a better way of doing it. But yes, sometimes you MIGHT need that extra strength.

That said, why do students often reach for the steel carabiners in some cases? It’s not strength. It’s size and durability. Generally the largest carabiners we have are steel and they work best in the eyeholes of the Ferno litters we use. The Ferno has a load limit of approximately 2.6 kN. (Note how much less this is than the carabiners holding the litter! At this point 25kN vs. 20kN isn’t really important).

Besides fitting better, the steel carabiners tend to be more resistant to dings and scrapes and other forms of damage. In other words, it’s not as simple as “use the stronger one”.  It’s more complex and really comes down to “use the one that best fits the situation.”

I’ve mentioned previously the use of passwords and how we have rules we often follow in regards to them. I think it’s always worth understanding the WHY of rules and when to best apply them. For some accounts, I might have an easy to remember password because if it IS breached, the harm will be negligible.  For example, I’m a moderator for the sci.space.tech group on USENET (look it up if you’re below 40!). I have to log in about once a week to moderate an article. That account has a fairly simple password because in the worst case scenario, if someone DOES breach the account, the most they could do is… approve the 1 or 2 articles that come in each week. So it’s a password that’s “secure enough” (i.e. nothing anyone is going to guess from knowing me) but easy to remember.

On the other hand, the login for my E*Trade account is far more secure because if someone could access that, I could lose a lot of money.

So it’s not always “use the stronger one” it’s “use the one appropriate to the situation.”

This applies to many areas of life.