Clash Royale CLAN TAG#URR8PPP

SSMS Results to Grid - CRLF not preserved in copy/paste - any better techniques?

When I have a result set in the grid like:

SELECT 'line 1
line 2
line 3'


or

SELECT 'line 1' + CHAR(13) + CHAR(10) + 'line 2' + CHAR(13) + CHAR(10) + 'line 3'


With embedded CRLF, the display in the grid appears to replace them with spaces (I guess so that they will display all the data).

The problem is that if I am code-generating a script, I cannot simply cut and paste this. I have to convert the code to open a cursor and print the relevant columns so that I can copy and paste them from the text results.

Is there any simpler workaround to preserve the CRLF in a copy/paste operation from the results grid?

The reason that the grid is helpful is that I am currently generating a number of scripts for the same object in different columns - a bcp out in one column, an xml format file in another, a table create script in another, etc...

4 Answers
4

This issue has been fixed in SSMS 16.5 build 13.0.16000.28 with the addition of an option to preserve CR/LF on copy/save (more details) (Connect bug).

This will cause CR, LF, and CRLF to be treated as newlines when you copy a cell.

CR

LF

CRLF

I believe this article has several techniques which can be useful: http://sqlblogcasts.com/blogs/martinbell/archive/2009/10/25/How-to-display-long-text-in-SSMS.aspx

SELECT CAST([field_with_crlf] AS XML)

One thing you can do is send results to a file, then use an editor capable of watching a file for changes which has superior capabilities for understanding the output.

it is a hack, but try this:

wrap your result set in a REPLACE (.....,CHAR(13)+CHAR(10),CHAR(182)) to preserve the line breaks, you can then replace them back

SELECT
REPLACE ('line 1' + CHAR(13) + CHAR(10)+ 'line 2' + CHAR(13) + CHAR(10) + 'line 3'
,CHAR(13)+CHAR(10),CHAR(182)
)


OUTPUT:

----------------------
line 1¶line 2¶line 3

(1 row(s) affected)


replace them back in SQL:

select replace('line 1¶line 2¶line 3',CHAR(182),CHAR(13)+CHAR(10))


output:

-------------------
line 1
line 2
line 3

(1 row(s) affected)


or in a good text editor.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.