How to insert or remove line breaks from SQL Server text columns and variables?
Tutorial to insert or remove line breaks from SQL Server strings stored in NVARCHAR columns or 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.
Table of Contents
1. Insert or remove line breaks with T-SQL
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. And of course, results are wrong because the columns are shifted.
The table is not structured as wanted because every line break creates 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.
2. Prepare a table with sample text data
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:
- Check if the Customers table exists and then drop it.
- Create the customers table.
- Insert one unique line of data, with multiple line breaks in the Address column.
- 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.
3. Remove MS SQL new line feeds with REPLACE
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
4. Insert line breaks into a T-SQL string
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:
-- 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.
5. A new line is Carriage Return + Line Feed
A carriage return (CR) is a control character or command in a text file. It causes the cursor to move to the beginning of the next line. The CR+LF sequence is used as an end-of-line marker in text files. Meaning that it tells the computer where one logical line ends and another begins. It can be found at the end of every logical line in a text file.
The carriage return and line feed combination is the most basic kind of end-of-line marker, called a literal or hard carriage return. A CR+LF sequence can be used to represent a new line of text, or it can be used to indicate that the following character should not be printed on the current line. It can be used to separate lines of text in a data file.
6. Questions about new lines in MS SQL
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 column, simply apply a replace method to remove it and replace with a space or an empty string.
In conclusion, carriage returns, or line breaks are not a big issue in SQL Server. They can be handled by making sure the SQL Server CHR(10) and CHR(13) are managed properly. The input controls help a lot to lower the number of chars inserted in the database.