How to Correctly Insert Newline in Nvarchar

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.

How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

I found the answer here: http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/

You just concatenate the string and insert a CHAR(13) where you want your line break.

Example:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT @text

This prints out the following:

This is line 1.

This is line 2.

SQL: Insert a linebreak in varchar string

Well your query works perfectly fine.
SSMS by default shows all query out put in the grid view, which does not display the line break character.

To see it you can switch to text view using cntrl + T shortcut or like below

The results I got for your query are below( and they work)

How to type a new line character in SQL Server Management Studio

You can't. (Edit: other good answers have been posted with some workable methods.)

My preferred method to do this is via a direct SQL update.

Two general techniques:

--Literal returns inside strings
UPDATE mytable
SET textvalue = 
'This text
can include
line breaks'
WHERE rowid = 1234

--Concatenating CHAR codes
UPDATE mytable
SET textvalue = 'This text' + CHAR(10) + CHAR(13) 
   + 'can include' + CHAR(10) + CHAR(13)
   + 'line breaks'
WHERE rowid = 1234

The former is a little easier to work with, but could give inconsistent results if you paste in text from outside sources with unknown line-ending codes.

The latter is somewhat harder to work with but is more likely to give you consistent and reliable results.

Append a New Line onto each concatenated row from SQL Server

My guess is that the CHAR(10) and CHAR(13) characters did in fact make it into your output, but that for whatever reason SSMS or your particular tool is not rendering them as you would expect. A workaround here might be to output your query to a text file and then open in a bona-fide text editor (e.g. Notepad++).

Replace a newline in TSQL

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

how to show the line breaks of a nvarchar string from the database in an html page using repeater

you can write a function in code behind that 'll do your logic. Inside it you simply replace the the characters CHAR(10) + CHAR(13) you get from database with <br/>

<asp:Repeater ID="myRepeater" runat="server">
    <ItemTemplate>
        <div class="dataTiltle">
            <%#Eval("datatitle") %> <br />
        </div>
        <div class="dataMain">
            <%#Your_Function_Name(Eval("datamain")) %>
        </div>
    </ItemTemplate>
    <SeparatorTemplate>
        <hr />
    </SeparatorTemplate>
</asp:Repeater>

then from code behind write your public function:

public string Your_Function_Name(string datamain)
{
//now u have the value from DB in datamain. Do your required logic here then return the required value
}

SQL - Inserting text with line breakes into a column causes errors

You can remove the carriage returns (char(13) or \r) and keep the line feeds (char(10) or \n).

---

Related Topics