Question: How to Insert Line Break in SQL Server String?
Answer: I personally prefer that SQL Server does not deal with string operations, as SQL language is honestly made for relational operations. However, now as we have this question presented in front us, let us let us try to answer it.
In SQL Server, just inserting a line break in SQL queries will show a line break.
There is no way to explain this so I am going to show you an image which describes how it works.
It is clear from the image that in SQL Server we do not have to insert any special character between line string.
However, to simulate this in SQL Server Management Studio, you need to enable your results in the text format (instead of grid format).
Well, with that said there is another alternative way where we can insert CHAR(13) in the line and it will also demonstrate the result.
Here is the screenshot of that particular code and it will be now clear how to insert a line break in SQL Server.
I personally prefer either of this method as needed by my application. There are some situations where I use the first method and there are some situations where I use the second method.
Which one of the above is your favorite method?
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
This seems to be a Windows-specific solution. Let’s say we have Java code running on a Linux client and we run the query. I expect a single line to render (the second line) because it will overwrite the first line. This is due to the fact that char(13) is carriage return, but the newline character in Linux is char(10), otherwise known as “line feed”.
Good Point, I was suspecting this would be Environment specific.
Personally I use those methods too, but a little different:
In the first example, I put a line break after the first quote like this:
Select ‘
This is the First Line
Second Line
‘
That permits me to Indent all the lines as much as I need.
As about the second example, as David Rosario pointed out, is a windows specific solution.
That is why I use both chars as line break:
Declare @LineBreak nvarchar(10) = char(13)+ Char(10),
@text nvarchar(100);
Select @text = ‘This is the First Line’ + @LineBreak + ‘Second Line’;
Using it like this, It works in Linux, Unix and old IOS tooo.
Some details about line break here:
https://stackoverflow.com/questions/1552749/difference-between-cr-lf-lf-and-cr-line-break-types
How we can add new line between values in select statement ,
like select col1 +’\n’ + col2
Not working
Eg:-
Declare @Text nvarchar(max)
set @Text=’Line 1′ + CHAR(13) +’Line 2′
select @Text