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

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

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:


March 13, 2009

  • I tried your version and I received an error. I believe it should actually be:

    select REPLACE(REPLACE(column_name, CHAR(10), ‘ ‘), CHAR(13), ‘ ‘) from table_name ;

    Or perhaps there is something I don’t understand about what the difference is between the two versions? I have very limited knowledge of SQL.

  • I see you don’t monetize your page, don’t waste your traffic, you can earn additional bucks every month because you’ve got high
    quality content. If you want to know how to make extra
    money, search for: Mertiso’s tips best adsense

  • Leave a Reply

    Your email address will not be published. Required fields are marked *