How to Insert Line Break in SQL Server String? – Interview Question of the Week #139

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.

How to Insert Line Break in SQL Server String? - Interview Question of the Week #139 linebreak0-800x245

In SQL Server, just inserting a line break in SQL queries will show a line break.

Solarwinds

There is no way to explain this so I am going to show you an image which describes how it works.

How to Insert Line Break in SQL Server String? - Interview Question of the Week #139 linebreak1

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.

How to Insert Line Break in SQL Server String? - Interview Question of the Week #139 linebreak2

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)

Solarwinds
, , ,
Previous Post
What does Keyword STATS Indicates in Backup Scripts in SQL Server? – Interview Question of the Week #138
Next Post
How to Create Temp Table From Stored Procedure? – Interview Question of the Week #140

Related Posts

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

    Reply
  • 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

    Reply
  • How we can add new line between values in select statement ,
    like select col1 +’\n’ + col2

    Reply

Leave a Reply

Menu