Query results omitting carriage returns/text formatting

Question

Post reply

Query results omitting carriage returns/text formatting

zoggling

SSCrazy

Points: 2243
More actions
December 1, 2016 at 3:06 am

#332278

I am sure there is a very simple explanation to this! We have found that in some environments, SQL Server Management Studio returns nvarchar/varchar results (including object definitions from sys.sql_modules) that are on a single line and lack any carriage return formatting.
Is there an option in SSMS that we are missing here?
For example, the following...
SELECT 'Here is
some text
with
carriage returns';
GO
... will be returned as follows in the results...
-- Copied from results pane:
'Here is some text with carriage returns'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

zoggling SSCrazy Points: 2243 More actions · Answer 1

I have found the solution:

https://msdn.microsoft.com/en-us/library/ms190078.aspx

Options | Query Results |SQL Server |Results to Grid Page | Retain CR/LF on copy or save

Open a new query window and the carriage returns should be preserved.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

You can also strip characters from a string using the REPLACE function. The ASCII characters for carriage return is (13) and it's typically followed by a line feed (10).

DECLARE @s-2 VARCHAR(200) = 'Here is

some text

with

carriage returns';

SELECT @s-2 = REPLACE(@s,CHAR(13)+CHAR(10),'');

PRINT @s-2;

Here issome textwith carriage returns

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

You can also opt to display your results to TEXT (rather than the default grid). The nature of a cell is such that the value takes priority over the format. Though the hidden characters are still there.

----------------------------------------------------