How to correctly insert newline in nvarchar

The problem is your setting on SSMS, not that the data doesn't have a line break.

Go to: Tools -> Options -> Query Results -> SQL Server -> Results to Grid -> Retain CR/LF on copy or Save and make sure the option is ticked.

You absolutely sure that it doesn't work? I did a quick check in SSMS with two different ways of result presentation.

Test code:

declare @rich_text_to_modify as nvarchar(200) = N'line1<BR>line2';

select @rich_text_to_modify

set @rich_text_to_modify = REPLACE ( @rich_text_to_modify , N'<BR>' , NCHAR(13)+NCHAR(10))

select @rich_text_to_modify

Presenting results using 'Results to Grid (Ctrl+D)'

enter image description here

Presenting results using 'Results to Text (Ctrl+T)'

enter image description here

As you can see your method works fine. In Grid end line characters are translated to spaces. Maybe there is a problem with the method you use for verification?

How to correctly insert newline in nvarchar

Related

Recent Posts