Question: How do you insert a line break into a SQL Server string?
Answer: I would usually leave display formatting to the application. SQL is happiest doing relational work. But sometimes a message, export, or report must contain the break as part of the returned string. There are two ways to get one into a literal or expression.
A literal can span lines
If you type an actual newline between the opening and closing quotes, that newline becomes part of the string. The original SSMS screenshot demonstrates it:
SELECT 'First line
Second line' AS MessageText;The exact historical literal, including its indentation, was:
SELECT 'This is First Line
Second Line';
Notice that the original second line is indented. The spaces in the literal become part of the value too. This makes hand-formatted SQL fragile if somebody later adjusts the indentation.
Build the break explicitly
For a Windows-style carriage-return-plus-line-feed sequence, use CHAR(13) + CHAR(10):
SELECT 'First line' + CHAR(13) + CHAR(10) + 'Second line'
AS MessageText;The original article used CHAR(13) alone. In its SSMS Results to Text view, that did show a new line:
DECLARE @text NVARCHAR(100);
SET @text = 'This is First Line' + CHAR(13) + 'Second Line';
SELECT @text;
CHAR(13) is a carriage return; CHAR(10) is a line feed. A consumer can display the lone carriage return differently, so the pair is clearer when the receiving program expects Windows line endings. Use a single line-feed if the receiving system specifically requires that format.
Viewing tip: SSMS Results to Grid may show the value in one cell without displaying its line break the way you expect. Switch to Results to Text to inspect the layout, and verify the behavior in the application that will consume the value. A screenshot of the grid alone does not prove the string lacks a newline.
I use the multiline literal for a fixed, short piece of text when its whitespace is deliberate. When the text is assembled from values, I prefer the explicit characters so a change in SQL formatting does not change the data. Which version is easier for you to maintain?
References: Microsoft CHAR control-character documentation and SSMS query result modes.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.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