I was working on another post when I found myself needing to dump out query results to a grid format to include in the post. This is a very normal situation - even in my day-to-day job I send emails with grids of results (we all do right?)
The easiest thing to do is to copy-paste from the SSMS grid results, and this is what I do 90%+ of the time just as I'm sure most of you do:
Execute with Results to Grid (usually the default), and then right-click in the upper-left of the the resultset and click "Copy With Headers" and then paste the data into Excel:
Easy right?
--
The gotcha is when you are returning something more interesting, something with punctuation like a query. When you copy-paste that your Excel turns out like this:
 |
| https://imagessure.com/thumbs/jziYuTJbBMxlZLZRfveokk5JIwpvVzi7NMi2yjIJGioHL02jChLnoqGXf-dlGY9gDVCQVMvX-DPD-BV9R4Mnjg.jpg |
The ugliness is usually caused by carriage return/line feed (CR/LF) in your query - we all like nice pretty TSQL, so we use lots of newlines along the way. For a normally delimited resultset, this results in lots of new rows as seen above.
The easiest way to deal with this is using the REPLACE function to replace the offending characters. Carriage Return and Line Feed are two different ASCII characters, CHAR(13) and CHAR(10) respectively.
To remove them with REPLACE, your code turns out like this:
--
SELECT REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
as QueryText
FROM <whatever DMV>
--
The code first replaces any carriage returns with a space, and then uses replace a second time to replace any line feeds with a space.
Using the REPLACEs without any other changes results into an Excel sheet that look like this:
Note the extra columns. When copy-pasting into Excel the presence of tabs causes the column breaks seen here.
Luckily, tabs are also replaceable as ASCII characters - CHAR(9):
--
SELECT REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '') /* Change TAB to Space */
as QueryText
FROM <whatever DMV>
Which now looks like this:
SELECT @DownloadCount=COUNT(*) FROM ( SELECT DISTINCT ma.Material_Attachment_ID FROM Topic_Main tm WITH (NOLOCK) INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE()) -->Query to calculate Experts based on Topic ID (PA)
Getting closer. Note that for some code, this may be the extent of what you need - you may not have all of that ugly whitespace.
To fix this last piece, let's try one more REPLACE to remove double spaces for single spaces:
--
SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '') /* Change TAB to Space */
, ' ', ' ') /* Change Two Spaces to Space */
as QueryText
FROM <whatever DMV>
--
Which now looks like this:
SELECT @DownloadCount=COUNT(*) FROM ( SELECT DISTINCT ma.Material_Attachment_ID FROM Topic_Main tm WITH (NOLOCK) INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE()) -->Query to calculate Experts based on Topic ID (PA)
--
 |
| http://www.quickmeme.com/img/98/98dd84943a5bcb086e5ec689072c0e6caa04bcc9314a37ae721268b5b798d533.jpg |
Better, but why didn't it solve our problem?
Replacing two spaces with one space does *not* replace three spaces with one space, or four spaces with one space, etc. Using this REPLACE simply turns every two spaces into one (or four spaces into two, or six spaces into three) - it *doesn't* clean up all the white space.
There are two ways to handle this - the first is a brute force method of using lots of REPLACE statements to repetitively replace two spaces with one as many times as you think is important:
--
SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ')
, ' ', ' ') as MyStringWithHopefullyNoMoreSpaces
--
In most cases with enough REPLACE statements this will work, but it is ugly.
The second, and more elegant, method is described by Jeff Moden (
blog) in an
article on SQLServerCentral and leverages the ability to REPLACE multiple characters at once.
For this method, instead of replacing a double space with a single space, we will replace it with what Jeff calls an "unlikely character" such as the Backspace (ASCII CHAR(8)):
--
SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, ' ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
as MyStringWithLotsOfSpacesAndNowBackSpaces
--
This makes a string that had multiple spaces now have a CHAR(8) as every other character.
Next, now that your former rows of strings ooooooooo instead looks like oxoxoxoxo, we will replace the flipped pattern, CHAR(8)+' ' with just an empty string - this removes all of the intermediate patterns:
--
SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, ' ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','') /* Change a Backspace and a Space to Nothing */
as MyStringWithAtMoseOneSpaceAndAtMostOneBackSpace
--
This removes all of the "xo" pairs so now the oxoxoxoxo becomes just o (a space). If there were originally an even number of spaces you would have had oxoxoxox and after the REPLACE you would end up with just ox (space+CHAR(8)) - this means you need one more REPLACE to strip off any remaining CHAR(8):
--
SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, ' ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','') /* Change a Backspace and a Space to Nothing */
, CHAR(8), '') /* Change Any Remaining Backspaces to Nothing */
as MyStringWithAtMostOneSpaceBetweenEachWord
--
As Jeff shows in his article, the result play out like this:
Original
String
(Odd Number) |
ooooooooo |
| Step 1 |
oxoxoxoxo |
| Step 2 |
oxoxoxoxo |
| Step 3 |
o |
| Final |
o |
|
|
Original
String
(Even Number) |
oooooooo |
| Step 1 |
oxoxoxox |
| Step 2 |
oxoxoxox |
| Step 3 |
ox |
| Final |
o |
--
Now that we know how to strip out those offending whitespaces, let's go back to our original query:
--
SELECT REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '') /* Change TAB to Space */
as QueryText
FROM <whatever DMV>
--
We now need to wrap this in our space-remover REPLACES like this:
--
SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '') /* Change TAB to Space */
, ' ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','') /* Change a Backspace and a Space to Nothing */
, CHAR(8), '') /* Change Any Remaining Backspaces to Nothing */
as QueryText
FROM <whatever DMV>
--
Is this ugly? heck yeah!
Does it work? HECK YEAH!
--
Original QueryText:
SELECT @DownloadCount=COUNT(*)
FROM (
SELECT DISTINCT ma.Material_Attachment_ID
FROM Topic_Main tm WITH (NOLOCK)
INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID
INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id
WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que
INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID
WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())
-->Query to calculate Experts based on Topic ID (PA)
Repaired QueryText:
SELECT @DownloadCount=COUNT(*) FROM ( SELECT DISTINCT ma.Material_Attachment_ID FROM Topic_Main tm WITH (NOLOCK) INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID= dad.Material_Attachment_ID WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE()) -->Query to calculate Experts based on Topic ID (PA)
--
This "repaired" text is easily paste-able into Excel into a single column, giving us an easily manageable spreadsheet.
...and all it takes is six REPLACEs!
Obviously you can *NOT* simply copy the "repaired" text into a query window and hit execute - if nothing else this method breaks inline comments which makes the code unexecutable. It is useful now for manual analysis and comparison - for example looking at expensive queries, or pattern matching for certain object names in the code - but not for execution.
--
Do I advise you to wrap all of your text fields in six REPLACES?
 |
| https://i.imgflip.com/t362n.jpg |
This is definitely an "edge" case - as mentioned above 90%+ of the time you will simply right-click, copy with headers, paste into Excel, and go on your merry way.
...but save this set of REPLACEs into your script repository - because sooner or later...you will need it.
I guarantee it.
 |
| https://memegenerator.net/img/instances/75015382/thats-the-fact-jack.jpg |
Hope this helps!
