Query Result To Excel with Messed Up Rows (SSMS 2012)Posted: March 28, 2014
When you export your query result from SSMS 2012 (not SSMS 2008) to a text file or copy and paste the result (with header), you may run into problem with columns (such as varcahr(max)…) that include Tab — char(9), Line feed — char(10) or Carriage return — char(13). You have garbled rows due to these invisible chars.
A quick fix is to remove them by using nested REPLACE functions in your SELECT query.
Here is and example:
--Sample code -- SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),''), CHAR(13),'') ,CHAR(9),'') as yourcolumn --
SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),”), CHAR(13),”) ,CHAR(9),”) as yourcolumn
If you want to remove them forever, you can use an UPDATE command to update these columns in your source table.