Horizontal Whitespace & Vertical Select

Another demo from the June SQL Pass / SSUG presentation. Some of the lesser known features of management studio is the ability to vertically select data (see below) and also delete white space the following is an example of using these.


You have been given a set of data product number / case number which is a string value that you need to run against a query.  You could copy these into Excel concatenate and add in (‘)   quotes before and after (‘) and add in commas (,) so you get a where clause like

SELECT * FROM table WHERE value in





Code to create some example data to use

SELECT top 50


THEN SPACE(ABS(CHECKSUM(NEWID()))%(1-10+1))+[ProductNumber]

ELSE [ProductNumber] END AS [Product number Example]

FROM [AdventureWorks2014].[Production].[Product]

This generates the following result set,  as can be shown every 3rd row (1), (2) has a random space value, to create a replication of varying product values


If we paste this into out query,   you  can see how the formatting is with the product number


So the product number ( every 3rd row contains a random space) to replicate product data being different lengths

(coolest feature in SQL Management studio alert warning, I really love this and use it a lot)

If you press the ALT key and drag your mouse cursor down vertically in SSMS

You can do a vertical select notice the thin blue line (1)


You can now press TAB and all the code would move right, if you type in ,’ you get

all the code wrapped in quotes and comma’s added, so if you had 300 lines , you would have got 300 comma’s added with just 3 key presses.

If you tab across on the first row (1) and do the exact same thing you have quickly added in quotes around the string values


Next to remove the white space select  Edit from the menu (1) and Advanced (2) and Delete Horizontal White Space


You then get all the white space removed ! Just like that


This entry was posted in Management Studio, Productivity, Tips & Tricks. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s