In my last Management Studio tip, I demonstrated how you can drag and drop column names to a query window. The only part I do not like about that is that all the column names are on a single line. I prefer to have on column name per line, and to quickly fix this we are going to use a Regular Expression (RegEx) within the Find and Replace dialog box.
For those not familiar with RegEx, it allows you to search through text using a multitude of options. In our example, we are going to use RegEx to quickly find and replace text in a script.
Below we have a single select statement that has all column names on a single line. We want to get each column name on a separate line and with the comma at the beginning of the column name.
Press Ctrl+H to open the Find and Replace dialog box. Then we need to click the RegEx icon.
In the Find box we want to find the comma and the space that follows it: “, “.
In the Replace box we want to enter “\r\t,”.
\r means we want to insert a carriage return.
\t means we want to insert a tab.
And follow that with a comma. Click the Replace All button.
Now we should have each column name on a separate line.
Next, we will use RegEx to get rid of the square brackets around the column names.
In the Find box we want to enter “\[|\]”.
\[ means we want to look for the left square bracket. The backslash is used to escape the left bracket since it is a RegEx special character.
| (vertical pipe) is used for an OR statement (i.e. we want to find either the left or right bracket).
\] means we want to look for the right square bracket. Once again, the backslash is used to escape the special character.
We want to leave the Replace box empty. Click the Replace All button.
Now we should see that our script is formatted in a way that is much easier to read.
These were simple examples using a small script, but RegEx can be an incredibly powerful when you need to search (and replace) text in a script that may be thousands of lines long.
Additional Reference:
https://docs.microsoft.com/en-us/sql/ssms/scripting/search-text-with-regular-expressions?view=sql-server-ver15
How do I go the other way? I often have columns on individual lines and want to put them on one line. I can’t seem to find what to search for that works. Great tip BTW.
Andy, always asking the difficult questions.
If you start with the final query in my example, you could just reverse the Find & Replace fields.
Find = “\r\t,” (carriage return followed by tab and a comma)
Replace = “, ” (there is a space after that comma)
I prefer tabs, but if you like spaces, then you could search for consecutive spaces.
Find = “\r\s{4},” (carriage return followed by 4 spaces and a comma)
Replace = “, ” (comma followed by a space)
I hope this helps.
I figured it out finally. It was Linefeed that I needed (\n). As always Patrick thanks.