Andrew Fraser DBA

Howto remove carriage return line feed from SQL Server for displaying in Excel

Use this SQL to remove carriage return line feed from SQL server for displaying in excel:

SELECT REPLACE ( column_name , CHAR(13) + CHAR(10) , ', ' ) FROM table_name
GO

Based on posts by Aaron Bertrand and David Seruyang.

Update 2015: Carriage return (chr13) + line feed (chr10) is the default line terminator for Windows, but those two could also be present singly, especially if coming from unix or mac, so to deal with all possible combinations use:

SELECT REPLACE ( REPLACE ( column_name , CHAR(13) , '' ) , CHAR(10) , ', ' ) FROM table_name
GO

Thanks to Anil Kumar and Jessica Bledsoe for pointing that out in the comments.
– – – – – – – – – – –
Oracle syntax is a little different, using CHR instead of CHAR. For just the windows specific carriage return + line feed:

SELECT TRANSLATE ( column_name , CHR(10) || CHR(13) , ',' ) FROM table_name ;

or for the more general case:

SELECT REPLACE ( REPLACE ( column_name , CHR(13) ) , CHR(10) , ', ' ) FROM table_name ;

Oracle SQL Developer (tools > preferences > environment) allows the user to input different line terminators even in Windows: