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

Question

Using ColdFusion and Microsoft SQL, we export data to an Excel spreadsheet using the cfx_excel plugin. The data contains varchar (6000), which has CHAR (13) / line breaks entered in each record.

Line breaks appear in square brackets each time a report is created in Excel format.

How can I remove CHAR (13) in a SQL query?

Thank.

Answer 1

try it

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

or just for choice

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 empty if you want a space then use '' instead of ''

Answer 2

char (10) char (13),

replaceList(textToReplaceIn,"#chr(10)#,#chr(13)#",",")

, 2 ,

replace(replace(textToReplaceIn,chr(10),"","all"),chr(13),"","all")