Line break with SQL Server

How to remove line break from a text with SQL Server ? If you store a string as a variable or a column in SQL Server, then it can contain line breaks. 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.

If you do a copy and paste into the SQL Server Editor or in your favourite text editor like Notepad++ for example, you’ll notice that the text may contain some line breaks.

For example, you can copy and paste a large amount of data from SSMS result to Excel with line breaks. The Excel sheet displays not aligned data, different from the original query result. The table is not structured as wanted because every line break creates a new line.

How to remove SQL Server line breaks from a text? 

You don’t have the same number of columns and lines anymore in your Excel file! These line breaks are often an issue in very large files because they are tricky to find and to fix. The solution is to remove them directly from the SQL Server query.

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.

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

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

-- 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');

-- Check the results
SELECT * FROM dbo.CUSTOMERS;

Then, if you copy & paste the Address Text 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.

To remove these SQL line break, use the SQL Server replace function.

Indeed, the CHAR(13) and CHAR(10) specific codes represent the characters of the line break, and then replace them with space or a comma, for example:

SELECT 	CustomerID, FirstName, LastName, 
        REPLACE(AddressText,CHAR(13)+CHAR(10),' '), 
        CountryCode, CountryName
FROM dbo.CUSTOMERS

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

123, Mount Gatineau Avenue, Ottawa

Adding a line feed to a T-SQL string

Conversely, to add a line break to a column in a query, simply add the two special characters below:

  • CHAR(13)
  • CHAR(10)

The query looks like this:

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

This post shows how to add or remove line break with a SQL Server query, you might also like this function to split a string into two parts delimited by any character.

What is a text line break ?

A line break or return carriage is when a line is cut in two or more parts and the rest of the current line appears in the next one.

What’s the best tool to edit a text file ?

One of the best software to edit a file is Notepad++ because it allows you to edit medium large files without crashing and also it recognizes many different languages like XML or SQL.

What’s the difference between special characters char(13) and char(10) ?

Both char(13) and char(10) special characters have to do with new lines in text files. Char(13) is the carriage return and char(10) is the line feed symbol.

How to remove line feed from a SQL Server column ?

To remove the carriage return or line feed directly from a SQL column, simply apply a replace method to remove it and replace with a space or an empty string.