SSMS Results to Grid - CRLF not preserved in copy/paste - any better techniques?
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).
- Tools > Options
- Expand Query Results > SQL Server > Results to Grid
- Tick Retain CR/LF on copy or save
- Restart SSMS
This will cause CR, LF, and CRLF to be treated as newlines when you copy a cell.
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 1line 2line 3(1 row(s) affected)or in a good text editor.
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.