Why Carriage Return or Line Break does not work as a default feature in MS SQL Server?
From some reason when I first ran MS SQL 2016 I realized when I run some code with Carriage Return and Line Break it stays in one line after coping that into a note pad or below the SQL code in Microsoft SQL Server Studio. As a Remote DBA services we add some debugging code into our stored procedures which we use these functions for line break or line feed. That was a really a necessary feature which was needed.
So, what happened here?
After digging deep into it I found out that this feature that worked as default from MS SQL 2014 and below is not working as a default feature anymore. You need to set it up in order to make that work.
Here is how you can set up that feature in MS SQL 2016 or any above version:
Click on Tools from the main menu and then Options first as shown below
Click Query Results and then Results to Grid. In this point just check mark: Retain CR/LF on copy or save in the right side.
Let’s have the following string as: Red, Green, Blue, Black, Yellow, Brown, Purple, Pink, Silver, Gold, Burgundy.
Let’s see what happens when that feature: Retain CR/LF on copy or save is UN-checked and then checked.
Run the above code and then copy and paste the below result in a text file or just below the above:
Red Green Blue Black Yellow Brown Purple Pink Silver Gold Burgundy
Basically it ignores the line break as CHAR(10).
With : Retain CR/LF on copy or save option is cheeked you see as follows:
You can add line break and line feed as follows:
See results how it looks with Line Feed as well:
In the above SQL samples , we used these functions as follows:
CHAR(10) – Line Break
CHAR(13) – Carriage Return
Note, that If you need to insert a tab then you can use the same code written above but with CHAR(9).
In any new version of MS SQ L there could be always changes and improvements. Some known features could be accessed differently. In this article I provided the steps of how to set the Line Break or Carriage Return the and provided some samples of using that code.
Check our SQL Consulting services:
Some of the technologies that we use: