How to correctly insert newline in nvarchar

user_5534804 0
Viewed 2932

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?

sql-server-2012 tsql sql-server sql
2 Answers

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.

user_20299830

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?

user_97097750
Related