How to manage SQL Server line breaks in text?

How to insert or remove SQL Server carriage returns in strings, columns and variables?

Tutorial to manage line breaks in SQL Server strings stored in NVARCHAR columns or T-SQL variables. For example, when exporting a the text to an excel file and some lines are split in two different parts. But with SSMS queries, if you select data containing carriage returns, the results still displays the full content of the line.

To check and see the issue, copy and paste data into the SQL Server query editor or in your favourite text editor like Notepad++. You might see line breaks there. Same use case after the integration of CSV files into SQL Server using SSIS for example. Indeed, depending on the size of the source files, it can be very tricky to identify all the error lines in detail.

You must automate this detection with a query and replace them to ensure proper integration.

1. SQL Server line breaks errors and use cases

First, why it is important to properly manage line breaks in SQL Server texts? Consider managing a significant amount of data, copy or export data from the SSMS query results to an Excel sheet. If the data quality of the source is bad, it can include some line breaks. In this case, it can cause inaccurate outcomes due to shifted columns.

Indeed, the data can not fit the table structure because each line break generates a new row, but with less columns. So an inconsistent number of columns and rows appears within the Excel file. Line breaks can be particularly problematic in large files. To fix the errors, it is recommended to eliminate the line breaks directly using T-SQL queries.

2. A new line is made of carriage returns and line feeds

In text files, the character known as a carriage return (CR) instructs the cursor to transition to the start of the next line.

In other words, the CR+LF sequence serves as a marker signifying the conclusion of one logical line and the start of another one within a given file. And this sequence is present at the end of each logical line in a text file. This sequence is employed to differentiate between lines of text within a data file.

3. Insert line breaks in a sample SQL table

First, let’s prepare a table with some sample data that contains line breaks. We split here the address column into three different lines. The query below performs 4 different SQL Server operations to insert line breaks that we’ll manage in the next steps of the this tutorial.

1. Check if the Customers table exists and drop it if necessary.
2. Create the Customers table.
3. Insert one unique line of data, with multiple line breaks in the Address column.
4. Select all the data from the table.

-- 1. If the table exists, drop it
IF EXISTS(  
	SELECT 1 FROM sys.objects
	WHERE object_id = object_id(N'dbo.CUSTOMERS') 
		AND type in (N'U') )
BEGIN DROP TABLE [dbo].[CUSTOMERS]
END;
GO

-- 2. Create the CUSTOMERS table
CREATE TABLE [dbo].[CUSTOMERS] (
	[CustomerID] INT IDENTITY(1,1),
	[FirstName] NVARCHAR(20),
	[LastName] NVARCHAR(20),
	[AddressText] NVARCHAR(100),
	[CountryCode] NVARCHAR(3),
	[CountryName] NVARCHAR(50)
);
GO

-- 3. Insert one line of data with line breaks to illustrate the use case
INSERT INTO dbo.CUSTOMERS ( FirstName, LastName, AddressText, CountryCode, CountryName )
VALUES ( N'SMITH',N'John', 
N'123,
Mount Gatineau Avenue,
Ottawa',
N'CA',N'CANADA');
GO

-- 4. Select to check the data
SELECT *
FROM dbo.CUSTOMERS;
GO

Then, if you copy and paste the AddressText column from the SQL Server result. You notice that the address contains two line breaks, as in the INSERT statement.

123,
Mount Gatineau Avenue,
Ottawa

 To continue, we’ll use the SQL Server REPLACE built-in function in the queries.

4. Remove SQL carriage returns with the replace function

Indeed, to remove the line breaks, simply use the replace function combined with the CHAR(13) and CHAR(10) codes.

These two specific codes represent the characters of the line break. Then replace them with space or a comma. For example to clean the Address column from new line feeds, we would use a query like this one bbelow:

-- Check the results
SELECT	CustomerID, FirstName, LastName, AddressText
FROM	dbo.CUSTOMERS;

-- Using the REPLACE function to remove the line breaks
SELECT	CustomerID, FirstName, LastName,
        REPLACE(AddressText,CHAR(13)+CHAR(10),' ')
FROM dbo.CUSTOMERS;

This time, the copy-paste gives us this result. It clearly displays the customer address with no more line breaks, now the three parts of the address are on the same line.

123, Mount Gatineau Avenue, Ottawa

You might also like this tutorial: How to split a string after a character with SQL Server ?

5. Insert line breaks in a text with the T-SQL CHAR function

The other way around this time, is to add a line break to a column or any text variable in a T-SQL query. Simply add the two special characters like in the code below.

Indeed, to do the opposite operation, use the same method, simply invert the arguments in the function, e.g. replace a space or a particular character by the two special characters:

• CHAR(13)
• CHAR(10)

-- Query to add a carriage return and build the full address
SELECT
	[CustomerID]
	,[FirstName]
	,[LastName]
	,[AddressText] + CHAR(13) + CHAR(10)
	+[CountryCode] + CHAR(13) + CHAR(10)
	+[CountryName] AS [FullAdress]
FROM dbo.CUSTOMERS;

To see the carriage returns, activate the Results to Texts option using the SSMS menu.

Managing carriage returns in T-SQL helps for data quality

In conclusion, it is relatively easy to manage line breaks in SQL Server, as long as they are properly understood. They can be handled by making sure the SQL Server CHAR(10) and CHAR(13) are replaced, or inserted if need be.

In general, the input controls in the applications help a lot to lower the number of invalid characters inserted in a database.

It allows to lower errors when formatting data manually and transferring it between applications like SSMS and Excel for example. But also automatically via scheduled jobs, when transferring it between databases or using APIs.

Questions about line breaks in SQL Server

More T-SQL tutorials on how to manage text data type

• Split SQL Server text with the XML functions.
• Manage SQL Server text with more than 8000 characters.
• How to split a string after a character in T-SQL?