Basic regular expressions are easy to use

When you want to do some formatting, these tricks make your life much easier.

Demo of selecting and typing on multiple lines starts ~4:20.

Transcript

Tabs vs spaces

When it comes to writing and editing TSQL, there are a lot of disagreements out there. Very passionate disagreements about whether we should have tabs, or whether tabs should all be replaced by spaces.

If you or your team has taken sides, you can easily configure Management Studio to agree with you and to help you be consistent with your team or your personal editing practices. In Tools, Options, under Text Editor, we can go into Transact-SQL and expand it, and then go to Tabs.

Here we can configure for new documents. Do we want to keep tabs or do we want to insert spaces? Also, how big should the tabs be? I’m going to say I want spaces instead of tabs. But, of course, this is just going to be for new stuff. Let’s say I have existing documents that already have tabs in them. And, see, this isn’t four spaces. I’m arrowing back and forth and this is a tab.

Using control + h find and regular expressions

If I want to, in my existing document, replace tabs with spaces, I just did control + h to bring up my replace box. I’ve got what I want to find and what I want to replace, and this isn’t what I want to use yet. I’m going to change this.

I do have this .* enabled. This says I want to use regular expressions. I’m going to use a special expression to say I want to find all the tabs. And I’m looking just in the current documents. So, I’m going to say find all of the tab characters.

And note that as soon as I put \t in there, as long as regular expression is checked off here, it highlights in orange and is like, oh, you’ve got some tabs right there, I’m going to find those for you. Well, I want to replace these. Not with some text, but with four spaces.

So, as soon as I do that, I can say replace all or alt + a, and it goes ahead and finds all of my tabs and replaces them with spaces. Now I can arrow back and forth. Of course I can do the opposite. I can say I want to find four spaces and replace them with a regular expression of a tab, replace them all, and now my spaces have become tabs again.

Whichever way you want to do it, you can use regular expressions to easily convert those so that you’re being consistent.

Formatting a query with regular expressions

You can also use regular expressions to format text. We’re going to use regular expression and a little trick in here to format a copy of this query against [Sales].[Customers]. This is the original one up here that I’ve already made a copy of. The first thing I want to do, right, this is all set up like I’ve just dragged the column list in. I want all of these columns to be on their own line.

I’m going to hit control + h. Since I had the query selected, it has defaulted to, okay, I’m only going to work on this selection. That’s good, that’s what I want. I’ve got my regular expressions on, I like this. Alright, so what we want to look for is every instance of a comma and a space. It helpfully previews that in orange for me. What I want to replace it with is a comma and then a new line.

So I want a comma and then I want it to go to a new line. And I want one, two, three, four spaces after it, so that these columns are all, at least in this section, on their own line. It does that for me, it’s easy to undo if I mess it up.

How to type on multiple lines at once with Alt+drag

But, hmmm, maybe I want to alias these. I’m going to go down here and hit Escape to get that dialogue box to go away. What if I actually want this to be known as cust? I’ve typed in my table alias here. Now I want to easily add this alias to all the lines. I’m going to make them consistent, I’m going to put my first line, just like everyone else, on it’s own line. Because I want to alias it as well.

Then, with my mouse here, I need to hold down the Alt key. Then I am dragging my mouse. See this yellow bar? This yellow bar is the magical, vertical column I have selected. It doesn’t even have to all fit on this screen at once.

What I did was I held down Alt and then just dragged my mouse.

Now, when I type, it is going to type on all of the lines. So I’m typing c, u, s, t. And note that Intellisense is kind of freaking out. This is one of those things. You may want to turn Intellisense off before you do this, because it looks super wacky. And I’m like, oh, Intellisense, I don’t want any of those. I’m going to hit Escape, make Intellisense go away, and then admire my work, where I’ve typed on all of the lines.

Then I’m going to test the query and make sure that it works. If I want to edit this now, I just need to hold down Alt. And, you know, if I only want to edit some of them.

What if these are actually from something alias ‘dist’, I could change it, I can then undo that. Very, very flexible. Let’s you easily type in a whole column, which is really, really cool.

So by using control + h to bring up my replace dialogue, and using regular expressions to make the lines, I was able to easily format that query into multiple lines, then alias it and put the alias there.

I actually think that’s really fun to do, and it makes editing queries lots more fun. I’m going to reset it in our query environment here, so that you can take a go at it. And have fun in the demo scripts as well.