Excel formatting SQL results

Sometimes its the little things that get annoying like requesting some information from a DBA or a system admin at a remote site where you can’t run scripts but need someone to do it for you. Quick example you want to know what servers they have across their estate and ask them to run basically SELECT @@Servername  and send the result back in excel.

SELECT @@VERSION AS [SQL Server and OS Version Info]

So you run across some servers and get the nicely formatted results

Version A

Copy and Paste to excel

Version B

and the Format is all over the shop, so a handy bit of code to have is the following. You can use it for stripping out carriage returns / line feed and tab. Just replace the @@version with whatever you want to remove the control characters from

</pre>
SELECT REPLACE(REPLACE(REPLACE(CAST(@@VERSION AS VARCHAR(MAX)), CHAR(13),''),CHAR(10),''),CHAR(9),'')   AS [SQL Server and OS Version Info]
<pre>
Control character Value
Tab char(9)
Line feed char(10)
Carriage return char(13)

and the result, nicely formatted. Simples

Version C

Advertisements
This entry was posted in Productivity, TSQL. 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