How to Insert a Line Break in a SQL Server Varchar/Nvarchar 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

Sample Image

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

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

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 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.



Related Topics



Leave a reply



Submit