We help IT Professionals succeed at work.

Preserve CarriageReturns in TSQL

SkyGuyDave asked
I have a legacy database in SQL 2005, and am using queries to extract data from the tables.  Some of the tables are text and contain imbedded Carriage Returns [chr(13)] to produce intentional multi-line fields.  The SELECT statements are used to extract data from the legacy database, put into Excel for data cleanup purposes, and ultimately loaded into a new database.

However, the imbedded CR's appear to be converted to blanks, so the resulting fields are single-lines.  How can I preserve the CR's from the legacy database in the SELECT statement output?

Thanks, Dave
Watch Question

do the value contain char(13) as something like:-
Formatting out the CR's is probably being done by Excel, not by your SELECT statements.

To verify, try running your query in SQL Server Management Studio with the output in Text mode (as opposed the the default Grid mode).

I expect you'll see the carriage returns intact.

If not, please post the SQL query ... as it could be using a REPLACE call to replace the CR's with blanks.


After posting my question, I found another post that helped ("Carriage returns in SQL table").  By switching SSMS into text mode, I can see the CR's are, in fact, still there.

Here's the flawed process which I had been using.  Execute the query in SSMS, click the blank cell in the upper left corner of the RESULTS tab, right-click that cell and choose "Copy with Headers", paste in Excl.  Because the SSMS tab was in grid mode, the CR's had already been replaced with spaces.

I'll have to find another process to get the data into Excel, such as "Save as File", which does not replace those special chars.