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.

Answer 1

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 ''

Answer 2

To replace both char(10) and char(13) you should be able to just do a replaceList(textToReplaceIn,"#chr(10)#,#chr(13)#",","). If that doesn't work you can just do 2 replaces as in replace(replace(textToReplaceIn,chr(10),"","all"),chr(13),"","all")