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:

line_terminator

Posted in Character sets, SQL server
6 comments on “Howto remove carriage return line feed from SQL Server for displaying in Excel
  1. Johnny says:

    Very good 🙂

  2. sajjan says:

    thanks!

  3. romildo says:

    Thanks, save my day.

  4. Anil kumar says:

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

  5. Jessica Bledsoe says:

    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.

  6. Brad says:

    Excellent! Thanks for sharing!

Leave a Reply

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

*