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 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
Why Is Select * Considered Harmful
Passing a Varchar Full of Comma Delimited Values to a SQL Server in Function
How to Implement a Many-To-Many Relationship in Postgresql
Are Postgresql Column Names Case-Sensitive
SQL Query Return Data from Multiple Tables
SQL Split Values to Multiple Rows
Insert into ... Values ( Select ... from ... )
SQL Join and Different Types of Joins
Get Records With Max Value For Each Group of Grouped SQL Results
Difference Between Lateral Join and a Subquery in Postgresql
MySQL Query Finding Values in a Comma Separated String
Get Value Based on Max of a Different Column Grouped by Another Column
Find a String by Searching All Tables in SQL Server