CODEWITHSUNDEEP
sqlt-sqlcoldfusion
Alex
Alex

Reputation: 1733

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

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.

Upvotes: 4

Views: 44500

Answers (2)

remotesynth
remotesynth

Reputation: 271

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")

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135061

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

Upvotes: 8

Related Questions