Shortcut: Replace Tabs with Spaces and do Macro-like work in SSMS using Regular Expressions

There are so many useful things in SQL Server Management Studio (SSMS) and daily, I come across users who aren’t aware of common useful functions.

A request that I hear all the time, is “I don’t like tabs but insert name of annoying colleague here decided he likes to use them. How do I remove them?” Now SSMS allows you to choose to use spaces instead of tabs (in Tools > Options) but that doesn’t fix existing tabs.

The regular expression functions in SSMS let you do this. Hit Ctrl-H to open the Find and Replace dialog (or use the Edit > Find and Replace > Quick Replace menus), then configure the window as follows:

Note that I’ve clicked the little square and asterisk thing (which says to use regular expressions), then put the change from text to \t which is a tab, and set the change to text to four spaces (or however many you want), then I click the Replace All button and magically all my tabs are replaced.

But this is just the start of what we can do.

For example, if I have a long row of comma-delimited values like this:

Now I could go to each comma and hit enter after each one. That’s what I see most people doing. But you could do this:

I’m changing a comma to a comma followed by a carriage return. And magically, this is the outcome:

There are so many things you can use this regular expression functionality for within SSMS. I encourage you to try it out.

2017-11-30