SQL Server - Replacing Carriage Return With Space

Asked By Jame

04-Apr-07 12:14 PM

I have a select query that I import to Excel. when I open it in Excel I
notice the rows with a carriage return dont work.
Is there a way to replace the carriage return char code with the space char
code durnig the execution of my sql select query.
Thes steps I am taking are:
1 In query designer I select Results To File...
2 I run my Select Query, saving as an rpt file
3 I open up Excel and import.
during step #2 in addition to selecting can I also parse columns and replace
carriage return with space. I am using sql server 2000. Thanks.

04-Apr-07 12:32 PM

SELECT REPLACE(REPLACE(REPLACE(column_name, CHAR(10) + CHAR(13), ' '),
CHAR(10), ' '), CHAR(13), ' ')
FROM table_name

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006