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. How to insert or remove line breaks from a text with SQL Server?
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.
SQL Server query to insert or remove line breaks from a text
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.
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.
-- 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 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.
Remove SQL Server line break with the replace function
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.
SELECT CustomerID, FirstName, LastName, REPLACE(AddressText,CHAR(13)+CHAR(10),' '), CountryCode, CountryName 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
Script to insert a line break to 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:
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.
Purpose of line breaks
A line break is a visual punctuation that indicates the end of one paragraph and the beginning of another. The use of line breaks in text can be traced back to ancient manuscripts, where scribes would separate different sections with vertical lines called rubrics.
These old books used both horizontal and vertical lines to divide up each page. Line breaks are important for two reasons, they create visual separation between paragraphs, and they create white space on the page.
The purpose of a line break is to give the reader a chance to pause. Which makes it easier for them to process what they have read so far. It also helps them avoid confusion. A line break is defined as “a paragraph or series of words that ends with one or more blank lines.” So line breaks are very important for readability and flow.
Line breaks and carriage returns
Carriage return is a term used in the computer programming world. It is a command that tells the program to start a new line of text on the screen.
A carriage return with SQL Server is an input character that tells the database to move down one row, and then start writing at the top of the next line.
Carriage return is also known as “newline” which is a code used by many programming languages to indicate that they have reached the end of one line of text and should start writing again at the beginning of another line.
A new line is commonly known as CR+LF (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.
Questions about line breaks
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 also it recognizes many different languages like 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.
This post shows how to insert or remove line breaks with a SQL Server query, you might also like this function to split a string into two parts delimited by a character.
In conclusion, carriage returns, or line breaks are not a big issue in SQL Server. They can be handled by making sure that 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.
Let’s go one step further and read another tutorial on string management in SQL Server. Here’s how to manage a VARCHAR text with more than 8000 characters.