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.
Table of Contents
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.
- Check if the Customers table exists and drop it if necessary.
- Create the Customers table.
- Insert one unique line of data, with multiple line breaks in the Address column.
- 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.
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 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. 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: 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:
-- 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 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.
Questions about line breaks in SQL Server
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.
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.
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.
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.