SQL 如何替换 DB varchar(6000) 中的 CHAR(13)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3525633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to replace CHAR(13) in DB varchar(6000)?
提问by Alex
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.
使用 ColdFusion 和 Microsoft SQL，我们使用 cfx_excel 插件将数据导出到 Excel 电子表格。数据包含一个 varchar(6000)，它在每个条目中输入了 CHAR(13)/换行符。
The line-breaks are appearing as square brackets every time the report is generated in Excel format.
每次以 Excel 格式生成报告时，换行符都显示为方括号。
How would I go about removing the CHAR(13) within a SQL query?
我将如何删除 SQL 查询中的 CHAR(13)？
Thank you.
谢谢你。
回答by SQLMenace
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
对于 char(10) 和 char(13) 你可以这样做
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 ''
'' 将用空格替换它，如果你想要一个空格然后使用 ' ' 而不是 ''
回答by remotesynth
To replace both char(10) and char(13) you should be able to just do a
要同时替换 char(10) 和 char(13) 你应该可以只做一个
replaceList(textToReplaceIn,"#chr(10)#,#chr(13)#",",")
If that doesn't work you can just do 2 replaces as in
如果那不起作用，你可以做 2 次替换，如
replace(replace(textToReplaceIn,chr(10),"","all"),chr(13),"","all")
