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:
|
1 2 3 4 5 6 7 |
CREATE TABLE [dbo].[CarriageDemo] ([text] [NVARCHAR](100) NOT NULL ) ON [PRIMARY]; GO SELECT * FROM [dbo].[CarriageDemo] |
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:
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:
|
1 2 3 4 |
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100); SET @strInput = ' January,February,March,May,June,July,August,September,October,November,December' Select @strInput as Input |
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:
|
1 2 3 4 5 |
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100); SET @strInput = 'January,February,March,May,June,July,August,September,October,November,December'; SET @strResult = REPLACE(@strInput, ',', CHAR(10)); SELECT @strResult AS 'String with Line Feed'; GO |
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:
|
1 2 3 4 5 |
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100); SET @strInput = 'January,February,March,May,June,July,August,September,October,November,December'; SET @strResult = REPLACE(@strInput, ',', CHAR(13)); SELECT @strResult AS 'String with Line Feed'; GO |
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:
|
1 2 3 4 5 |
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100); SET @strInput = 'January,February,March,May,June,July,August,September,October,November,December'; SET @strResult = REPLACE(@strInput, ',', CHAR(10) + CHAR(13)); SELECT @strResult AS 'String with Line Feed'; GO |
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:
|
1 2 3 4 5 |
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100); SET @strInput = 'January,February,March,May,June,July,August,September,October,November,December'; SET @strResult = REPLACE(@strInput, ',', CHAR(9)); SELECT @strResult AS 'String with Line Feed'; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE [dbo].[EmployeeData] ([EmpID] INT IDENTITY(1, 1), [FirstName] NVARCHAR(20), [LastName] NVARCHAR(20), [Address] NVARCHAR(100) ); INSERT INTO [dbo].[EmployeeData] (FirstName, LastName, Address ) VALUES (N'Rajendra', N'Gupta', N'123, Maruti Kunj, Gurgaon' ); |
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:
|
1 2 |
Select EmpID, FirstName, lastName, REPLACE(Address,CHAR(13)+CHAR(10),' ') as address from EmployeeData |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023