Link to home
Start Free TrialLog in
Avatar of Dave Credicott
Dave CredicottFlag for United States of America

asked on

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
Avatar of TempDBA
TempDBA
Flag of India image

do the value contain char(13) as something like:-
DeliciousChar(13)Food
ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dave Credicott

ASKER

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.