How to remove SQL Server new line?

How to remove SQL Server new line? This post propose a simple SQL Server query to process a string variable and remove the new line character. Technically we replace the new line by nothing. The SQL Server code for a new line is CHAR(13) + CHAR(10).

You want to remove it to have your SELECT statement “clean” and to avoid short text in two different lines. For example if you store and display a first name in one single line. You might have a user entering its first name in two different lines or more. To illustrate it, let’s initialize the first name as “John Alexander Junior” but in three different lines.

DECLARE @Firstname NVARCHAR(50);
SET 	@Firstname = 
N'John
Alexander
Junior';

SELECT @Firstname;

How to replace the new line characters from a SQL Server variable?

If you display it the default way in SQL Server Management Studio, then the text looks good, from from the version 2016 and upper. Then, if you click on the “Results to Text (CTRL + T)” option from the toolbar the result, SSMS displays it in three different lines. And stores it like this in your table, i.e. with the new lines!

To delete these characters from the string, use this SQL Server query with the replace() and the char() SQL Server function.

DECLARE @Firstname NVARCHAR(50);
SET 	@Firstname = 
N'John
Alexander
Junior';

SELECT @Firstname;
SET @Firstname =  REPLACE(@Firstname,CHAR(13)+CHAR(10),' ');

SELECT @Firstname;
Screenshot on how to remove SQL Server new line?
Result of the SQL Query before and after applying the replace function. 

Another version of the post with a more complete example is also available, it shows how to remove line breaks from a string in a table.

Check this article for more informations on the special characters and the char() T-SQL function here on Microsoft official website.

To conclude, replacing new lines can avoid many errors in data loads. With this tip in place, the system avoid to split one single record in two.

To go further on the text management topics, check how to split long text into smaller pieces based on a separator with a SQL query, very useful for XML.

Be the first to comment

Leave a Reply

Your email address will not be published.


*