Sql: Insert a Linebreak in Varchar String

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 insert a line break in a SQL Server

Is the \r\n still in the string when you use the inserted content or will this vanish?

Try this in SSMS:

declare @tbl TABLE(testString VARCHAR(100));
insert into @tbl VALUES('test with \r\n encoded line break')
                      ,('test with' + CHAR(13) + CHAR(10) + 'windows line break') 
                      ,('test with' + CHAR(10) + 'simple line break');

Now switch to (output to text (ctrl+T) )

SELECT * FROM @tbl;

The result:

test with \r\n encoded line break

test with
windows line break

test with
simple line break

With "output to datagrid (ctrl+D)" you will not see the line breaks anyway...

how to insert a line break after every second loop in t-sql stuff function

You could use a CASE expression to see if the value of ROW_NUMBER is divisible by 2, and if not then add a carriage return and line break:

SELECT STUFF((SELECT CASE WHEN ROW_NUMBER() OVER (order by new_nam) % 2 = 1 THEN CHAR(13) + CHAR(10) ELSE '' END + ', ' + new_nam
              FROM new_subcatagories
              ORDER BY new_nam
              FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 4, '');

SQL insert statement with carriage return or line feed

To insert the string that you are asking for, just do:

insert into table (description) 
values ('This is a description. Updated description.'); 

If I have to disregard your example and guess what you are asking, it would be something like this (depending on your RDBMS-provider, which I guess is mssql):

insert into table (description) 
values ('This is a description.' || CHR(13)|| CHR(10) || 'Updated description.'); 

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.

---

Related Topics