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 remove SQL Server new line?

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 remove the new lines from the string, use this SQL Server query with the replace 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 Remove SQL Server new line
SQL Server remove new line

 

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.

 

 

Be the first to comment

Leave a Reply