How to Insert Carriage Return or Line Break Or Tab into a string in MS SQL Server

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:

Step 1:
Click on Tools from the main menu and then Options first as shown below

Step 2:
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:

Red
Green
Blue
Black
Yellow
Brown
Purple
Pink
Silver
Gold
Burgundy

You can add line break and line feed as follows:

See results how it looks with Line Feed as well:

Red

Green

Blue

Black

Yellow

Brown

Purple

Pink

Silver

Gold

Burgundy

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).

Conclusions:
--------------------
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:

For more info about our MySQL and MS SQL consulting services we provide please call us: 732-536-4765 or send us an email via our Contact Us page.

Some of the technologies that we use:

• Alpha AnyWhere Development
• Dot Net Development.
• SQL Consulting Services.
• MySQL Database.
• MS SQL database engine.
• Web Scraping Development while using dot net.
• Convert MS Access To Web-Based Application.
• Optimize Your Database.
• Maintain Visual FoxPro Applications