sql - 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?

Answer 1

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.