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 ( '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