How to correctly insert newline in nvarchar

Question

I need to make a script that takes some rich text (html text) and transform it to regular text. What I want to do is replace all <br> tags with newline. I tried to do this with replace function:

set @rich_text_to_modify = replace(@rich_text_to_modify,'<br>', CHAR(13)+CHAR(10))

The
tags get removed but newlines are not inserted. Any idea what I am doing wrong?

Why do you think that isn't inserting a new line? It works fine for me. For example `DECLARE @rich_text_to_modify nvarchar(MAX) = 'Hello
Goodbye'; SET @rich_text_to_modify = REPLACE(@rich_text_to_modify, '
', CHAR(13) + CHAR(10)); PRINT @rich_text_to_modify;` returns 2 lines in the messages pane, `Hello` and `Goodbye`. — Thom A, Nov 02 '18 at 09:04
when nvarchar was updated I tried to copy it into editor to see if there are any line breaks but there wasn't any — OjamaYellow, Nov 02 '18 at 09:05

score 30 · Accepted Answer · answered Nov 02 '18 at 09:07

30

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.

answered Nov 02 '18 at 09:07

Thom A

88,727
11
45
75

1

For me, checking this option didn't work. I still get all text in one line, in spite of having many CR/FL characters in my text. – Saeed Neamati Aug 13 '19 at 10:38
I actually very highly doubt that @SaeedNeamati . I suspect that the setting wasn't saved, or you're doing something *else*. – Thom A Aug 13 '19 at 10:46
3

After checking the option, I found you have to start a new query window. It doesn't seem to affect one that is already open. It still shows as a space in the grid, but if you copy/paste it to Excel it will have the crlf. – pghcpa Jun 26 '21 at 01:29

score 6 · Answer 2 · edited Feb 05 '20 at 15:01

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)'

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

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

2 Answers2

Linked

Related