Removing trailing spaces in SSMS or Visual Studio

Regular expressions can easily and quickly remove trailing spaces from every line in your query window as well as a few other tasks I’ll explain. To get started, you need to know how to specify the end of a line. There are two ways to specify the end of a line. You can use the “\n” escape sequence or the end of line metacharacter “$”. Figure 1 shows how to remove a single trailing space from however many lines end that way. A more robust regular expression is shown in Figure 2. And a reader offers a keyboard shortcut to do the same thing without using regular expressions – see the comments section at the bottom. Removing extra blank lines is also described at the end of the post.

The problem with the syntax shown in Figure 1 is that you’ll have to iteratively click Replace All multiple times if you have more than one trailing space on some lines.

There is an easy fix that will remove all trailing spaces from all lines with a single Replace All. Add a “+” sign after the space and before the “\n” escape sequence. In other words, you want to use “ +\n” as your search string.

Notice that I used “ and ” to delimit my find and replace strings. As the screen captures show, there aren’t any quotes around the find and replace strings.

Once you understand the pattern of using the “+” metacharacter, you can extend it to other edits such as normalizing inline comments to be exactly two consecutive dashes. Sometime you will see inline comments with more than two consecutive dashes. Use “--+” as your search string and “--” as your replacement string. Don’t use “-+” as your search string because it would change a single dash to two consecutive dashes. That’s why you should use use “--+” to find all occurrences of two or more dashes.

Once you understand the “\n” escape sequence for the end of a line, you can use this knowledge to remove superfluous blank lines. Sometimes when copy and pasting from a web page or email, you end up with a blank line inserted after each line in the source. This is easy to fix using regular expressions. When a blank line follows a line of text, you have two consecutive newline characters. Use “\n\n” as your search string and “\n” as your replacement string to remove the superfluous blank lines as shown in Figure 5.

Figure 4. Regular expression to remove superfluous lines when every other line is blank.

Comments

Leave a Comment

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way. Follow @JohnPaulCook