How to replace CHAR(13) in DB varchar(6000)?

Question

Using ColdFusion and Microsoft SQL we are exporting data to an Excel Spreadsheet using the cfx_excel plugin. The data contains a varchar(6000) which has CHAR(13)/line-breaks inputted in each entry.

The line-breaks are appearing as square brackets every time the report is generated in Excel format.

How would I go about removing the CHAR(13) within a SQL query?

Thank you.

Solution

try this

update YourTable
set YourColumn =replace(YourColumn,CHAR(13),'')

or just for a select

SELECT replace(YourColumn,CHAR(13),'')
FROM YourTable

for char(10) and char(13) you can do this

SELECT replace(replace(YourColumn,CHAR(13),''),CHAR(10),'')
FROM YourTable

'' will replace it with a blank, if you want a space then use ' ' instead of ''