SQL SQL Server

How To Convert Confusing Ascii Code To A Character

CHAR can be used to insert control characters into character strings. The frequently used control characters are:

  • CHAR(9) = Tab
  • CHAR(10) = Line Feed
  • CHAR(13) = Carriage Return

My problem was trying to export data in Management Studio from a 3rd party application database into a spreadsheet.

I found knowing this useful when a column contained carriage returns as delimiters (which looked like spaces in Management Studio) so my SQL statement turned out to be something like this:

--; can be change to whatever is useful
REPLACE(column_with_carriage_return_delimiters, CHAR(13), ';')

Now I could copy/paste the result in a text editor and replace ; with whatever I needed. There maybe a better solution but this is what I came up with so far.