How to replace carriage return and line feed in a SQL server varchar field

 

I have been facing an issue while importing some data from SQL to CSV file. In one of my SQL fields user entered Enter Key or Line feed, because of that my exported CSV file was not in good format. Rows were broken in between. So I started searching for a solution to replace carriage return and line feed and finally accomplished by writing below query.

SELECT replace ( replace(Col1, char(10),''), char(13), '') as [Column 1] FROM  Test

In the above code "col1" is the column name where you have enter key or line feed and "Test" is the table name.

After using this query I was able to export the data to CSV with proper formatting as I required.

I hope this query may help you also.