How to remove enter (new line) space from column value in sql

I was recently doing a SQL table data export in to excel that included a plain-text data fields with  New line (Enter) in to same column. On data insertion in to column some time we put Bullets, Numbering and Enter(New line). When business required to get that data we can simple use select query its fine for get data in our application or in SQL grid result. But if we required the same data to export in to Excel or CSV file then you will find some strange result. In my condition there is requirement to get data in to Excel which is having New line into column data. When I tried to copy data from SQL query result to Excel uunfortunately, in Excel result i found new row, where line break exist in query result.

Then I tried to do Trim (LTRIM/RTRIM) for result column, Still problem was same. As we know TRIM does remove White Space, blank space.

• SELECT LTRIM(RTRIM(YourColumnName)) FROM TableName

I started how is it happening then, I tried to get ASCII char for New line (\n). When I tried to print the new line character using the ASCII function it was showing 10 which is new line character and 13 for carriage return(\r).

• Declare @myVar VARCHAR(100);
• SET @myVar='My new line string';
• SET @myVar=@myVar+CHAR(10)+'Here new line';
• SELECT @MyVar;

Result:- 
My new line string
Here new line.

• Declare @myVar VARCHAR(100);
• SET @myVar='My new line string';
• SET @myVar=@myVar+CHAR(13)+'Here carriage return';
• SELECT @MyVar;

Result:- 
My new line string 
Here carriage return.

After digging in to code finally I got solution with replacement function with ASCII code. Here is the exact solution.

Declare @myVar VARCHAR(MAX);
SET @myVar = 'My new line string';
SET @myVar = @myVar + CHAR(13) + CHAR(10) + 'Here result for single line';



SELECT REPLACE(REPLACE(@myVar, CHAR(13), ' '), CHAR(10), ' ');


Result:- 

My new line string Here result for single line.
Finally i got expected result..Happy coding :-).