Remove SQL line breaks from text with a simple query

How to remove SQL line breaks from a text or string stored as NVARCHAR? 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 paste into the SQL Server Editor or in your favorite 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 create a new line. 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.

 

 

How to remove SQL line breaks from a string?

 

First, let’s prepare a table with some sample data that contains line breaks. We are splitting here the address column in three different lines.

-- If table exits, 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 AddressText column from the SQL Server result. You notice that the address contains 2 line breaks, as in the INSERT statement:

123,
Mount Gatineau Avenue,
Ottawa

 

To remove these SQL line breaks, use the SQL Server replace function. Indeed, the CHAR(13) and CHAR(10) specific codes represent the characters of the line breaks,and then replace them with a 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. The copy paste 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

 

This post shows you how to remove line breaks from an SQL Server string, you might also like this function to split string into columns delimited by any character.

 

To go further, this is the SQL Server REPLACE built-in function documentation.

 

 

Be the first to comment

Leave a Reply