SQL Carriage Returns or Tabs in SQL Server strings (2024)

This article explores inserting SQL carriage return AKA line break and tab in a string along with SSMS behavior while copying data to excel, notepad.

Introduction

We work with various data types in SQL Server such as int, float, XML, char, varchar, etc. We also use strings for storing data such as the name of employee, company, product review, and feedback. Sometimes, we require data formats such as inserting a line break, tab or carriage return in a string. We might require these formatting while dealing with data in multiple sources such as flat or excel file. Occasionally, you see scattered text after copying data in notepad or excel.

Problem simulation

Let’s simulate the problem using the following query:

In the SSMS output, we see all text in a single line:

Let’s copy the output in Excel and Notepad. You can see that in both notepad and excel sheet row one and two splits in multiple lines. It retains the carriage return as well while copying the output in notepad or excel:

SSMS behavior while copying data

We might get different behavior of the carriage return with different versions of SSMS. SSMS 2016 and higher removes carriage return. Without carriage return, the output comes in a single line for each row. SSMS 2014 and previous version retains carriage property, and we are split output across multiple lines:

• SQL Carriage Return (CR): The Carriage Return moves the cursor to the beginning of the line. It does not move to the next line
• Line feed (LF): The line feed moves the cursor to the next line. It does return to the beginning of the line

SSMS allows us to define the carriage return behavior as well. Navigate to Tools | Options | Query Results | SQL Server | Results to Grid.

In SSMS 2016 and higher, we can see that “Retain CR/LF on copy or save” checkbox is not ticked. It shows that while copying output to notepad or excel sheet, SQL Server does not retain SQL carriage return on copy/save:

Let’s observe the output difference.

• With the enabled option of retain CR/LF on copy or save:

  

• With the disabled option of retain CR/LF on copy or save:

  See Also

  A Comprehensive Guide to Data Types in Oraclewhy is it (10,2) in particular ? – Q&A Hub | 365 Data ScienceAbout Oracle Database Release NumbersOracle Live SQL - Script: Get the top-N rows per group

  

Insert SQL carriage return and line feed in a string

We might require inserting a carriage return or line break while working with the string data. In SQL Server, we can use the CHAR function with ASCII number code. We can use the following ASCII codes in SQL Server:

• Char(10) – New Line / Line Break
• Char(13) – Carriage Return
• Char(9) – Tab

Let’s explore these ASCII codes with CHAR functions with examples.

Inserting line break or new line

Suppose we have a string that contains a month’s name. We use a comma to separate the name of the month. Execute this query in SSMS and view output in Result to text (short cut key CTRL + T) format:

Now, we want to insert a line break after each month’s name. We can replace the comma (,) with a char(10) function. As described earlier, char(10) inserts a line break.

In the following query, we use the SQL REPLACE function for replacing the comma with char function:

In the output, we can see a line break after each month. It formats data with a line break, and only one row gets affected due to this:

Inserting SQL carriage return

We use the Char(13) function for inserting a carriage return instead of a line break:

Inserting SQL carriage return and line break in a string

In previous examples, we used Char(10) and Char(13) individually for carriage return and line break, respectively. We can use both functions together for inserting a carriage return and line break:

The output of the above query with Char(10) and Char(10) is as shown below:

Inserting tab

Sometimes we insert tab between characters for formatting purposes. We can insert tab space in a string using the Char(9) function:

In the output, you can string format with a tab between each month:

Remove line break

Suppose we have a table that contains data with a line break. In the following table, we have a line break in the address column:

We use Char(13) for identifying and removing Carriage Return and Char(10) for removing line break along with the SQL REPLACE function. The replace function replaces line break with a space as specified in the query:

In the output, we can see the address field without the line break.

Conclusion

In this article, we explored the process for adding and removing a SQL carriage return and line break from a string. We also learned about the SSMS behavior for retaining carriage return and line break while copying the output to notepad or excel sheet.