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.

Scenario

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

(

'A1001'

,'A1002'

)

Code to create some example data to use

SELECT top 50

CASE WHEN ROW_NUMBER() OVER(ORDER BY ProductNumber) % 3=1

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