When I have to generate a report to output to a CSV file or text file, I sometimes have a problem with the carriage return. The following is my tip to replace the carriage return in SQL with a space.

Just keep in mind that when replacing the new line in a SQL statement or SQL script, you need to handle CR, LF and CR+LF. To replace the new line command with space, use the following:

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:


select REPLACE(REPLACE(@mystringhere, CHAR(13), ' '), CHAR(10), ' ')