How to insert or remove SQL Server line breaks from text ?

How to manage line breaks inside SQL Server text columns and variables?

Tutorial to insert or remove line breaks from SQL Server strings stored in NVARCHAR columns or T-SQL variables. For example, if you export the string to excel, your line will be cut. Indeed, with SSMS, if you select data containing carriage return or new lines, then the screen displays the full content of the line.

After a copy and paste into the SQL Server Editor or in your favourite text editor like Notepad++ for example, the text may contain some line breaks. Or after the integration of CSV files using SSIS, depending the size of the source, it can be tricky to sort all out and identify the error lines in detail.

1. Issue with line breaks and common use cases

Consider a scenario where a significant amount of data is copied from SSMS query results to an Excel sheet, including line breaks. In this case, the data displayed in Excel may appear misaligned and differ from the original query results, causing inaccurate outcomes due to shifted columns.

The desired table structure is compromised because each line break generates a new row, resulting in an inconsistent number of columns and rows within the Excel file. Line breaks can be particularly problematic in extensive files, as they are difficult to locate and resolve. To address this issue, it is recommended to eliminate line breaks directly from the SQL Server query.

2. The composition of a new line is carriage return and line feed

In text files, the control character known as a carriage return (CR) instructs the cursor to transition to the start of the subsequent line. The CR+LF sequence serves as a marker signifying the conclusion of one logical line and the commencement of another within these files. As such, this sequence is present at the end of each logical line in a text file.

The combination of carriage return and line feed constitutes the most fundamental form of an end-of-line marker, referred to as a literal or hard carriage return. The CR+LF sequence can represent a new line of text or indicate that the next character should not be displayed on the existing line. This sequence is employed to differentiate between lines of text within a data file.

3. Insert line breaks into a sample table

First, let’s prepare a table with some sample data that contains line breaks. We are splitting here the address column into three different lines. First execute the query below. It performs 4 different SQL operations:

1. Check if the Customers table exists and then drop it.
2. Create the customers table.
3. Insert one unique line of data, with multiple line breaks in the Address column.
4. Selects 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 first sales amount for each month
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 go further, this is the SQL Server REPLACE built-in function documentation.

4. Remove SQL Server carriage returns using the replace function

Indeed, use the simple replace function and 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.

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

5. Insert line breaks into a string using T-SQL CHAR function

The other way around this time, to add a line break to a column or any text variable in a T-SQL query, simply add the two special characters like below. Indeed, to perform the opposite, use the same method, but invert the arguments in the function, e.g. replace a space or a particular character by the couple:

• 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 from within SSMS.

Conclusion on managing carriage returns in T-SQL

In conclusion, carriage returns are not a big issue in SQL Server, as long as they are properly managed. They can be handled by making sure the SQL Server CHAR(10) and CHAR(13) are managed properly. The input controls help a lot to lower the number of invalid characters inserted in a database.

It useful especially in the formatting of data, when transferring it between applications like SSMS and Excel for example. This tutorial has provided you with the necessary information to insert or remove line breaks in SQL Server text columns and variables. By using the REPLACE and CHAR functions, you know how to work with carriage returns and line feeds. These scripts can help you maintain the integrity of your data and prevent errors caused by misaligned columns or inconsistent row counts.

Split a string after a character with SQL Server

Questions about the new line character in MS SQL

T-SQL tutorials on how to manage texts

• 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?

SQL Server string longer than 8000 characters