Preserve CarriageReturns in TSQL

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

do the value contain char(13) as something like:-
Daniel WilsonCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SkyGuyDaveAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.