How To Manage Line Breaks in SQL Server ?

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

Transact-SQL scripts to manage line breaks in SQL Server strings stored in columns or variables. In other words how to remove them or to insert them to streamline the data integration processes. For example, after the export of a SQL query from SSMS to an Excel document, some lines are split in two different parts. And the lines are broken. This is due to input errors in sources software including line breaks.

To reproduce this issue, copy data from your SSMS query results and paste it into your favourite text editor like Notepad++. You might see line breaks there. Same use case during the integration of CSV files into SQL Server using SSIS, these line breaks cause errors during the package execution or inconsistent data in the target table. And depending on the size of the source files, it can be very tricky to identify all the error lines in detail. Hence, you must automate this detection with a query and replace them to ensure proper integration.

About carriage returns and line feeds in text files

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.

1. Manually insert line breaks in a sample SQL Server 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.

2. Remove line breaks using the REPLACE SQL Server function

Indeed, this time, to remove the line breaks, simply use the REPLACE SQL function combined with the CHAR(13) and CHAR(10) codes. These two specific codes concatenated represent the characters of the line break. Then replace them with an empty string, a space or a comma, depending on the use case. For example to clean the Address column from line breaks, use a query like this one below.

-- 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, cleaned. 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: explaining How to split a string after a given character with SQL Server ?

3. 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, we 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;

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

Manage carriage returns using T-SQL scripts helps for data quality

In conclusion, it is relatively easy to manage line breaks using SQL Server queries, 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.

Indeed, it allows to significantly lower errors when formatting data manually and transferring it between applications like SSMS and Excel for example. And moreover the data integration errors in batches. Like automatic data exchange via SSIS scheduled jobs, or during transfers between databases or using APIs.

SQL Server string longer than 8000 characters

Questions about line breaks in SQL Server

More T-SQL tutorials about managing the 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?