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
	SELECT 1 FROM sys.objects
	WHERE object_id = object_id(N'dbo.CUSTOMERS') 
		AND type in (N'U') )

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

-- 3. Insert one line of data with line breaks to illustrate the use case
INSERT INTO dbo.CUSTOMERS ( FirstName, LastName, AddressText, CountryCode, CountryName )
Mount Gatineau Avenue,

-- 4. Select to check the data

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.

Mount Gatineau Avenue,

 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

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

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
Insert or remove line breaks in SQL Server using the REPLACE and CHAR functions
Insert or remove line breaks in SQL Server using the REPLACE and CHAR functions

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
	,[AddressText] + CHAR(13) + CHAR(10)
	+[CountryCode] + CHAR(13) + CHAR(10)
	+[CountryName] AS [FullAdress]

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.

SQL Server string longer than 8000 characters

Questions about line breaks in SQL Server

What is a line break in a text file?

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 it recognizes many other languages like HTML, CSS, PHP, XML or SQL.

What’s the difference between the 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 Server column, simply apply a replace method to remove it and replace with a space, an empty string or any other text character.

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

Scroll to Top