THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

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.

image

Figure 1. Replace space newline “ \n” with newline “\n” to remove one trailing space.

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.

image

Figure 2. Replace space plus newline “ +\n” with newline “\n” to remove all trailing spaces.

Alternatively, you can use the end of line metacharacter expression +$ as your search string and have no replacement string.

image

Figure 3. Replace space plus dollar “ +$” with nothing to remove all trailing spaces.

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.

image

Figure 4. Replace dash dash plus “--+” with dash dash “--” to normalize inline comments at two 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.

image

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

Published Wednesday, March 05, 2014 6:25 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Graham said:

You can also highlight your text and press Ctrl-k,\

March 6, 2014 10:51 AM

Leave a Comment

(required) 
(required) 
Submit

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.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement