SQL Oracle REPLACE() 函数不处理回车和换行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/407027/
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
Oracle REPLACE() function isn't handling carriage-returns & line-feeds
提问by Martin Cowie
We've a table with a varchar2(100)column, that occasionally contains carriage-return & line-feeds. We should like to remove those characters in the SQL query. We're using:
我们有一个带有一varchar2(100)列的表格，其中偶尔包含回车和换行符。我们想删除 SQL 查询中的那些字符。我们正在使用：
REPLACE( col_name, CHR(10) )
which has no effect, however replacing 'CHR(10)' for a more conventional 'letter' character proves that the REPLACE function works otherwise. We have also found that
这没有效果，但是将 'CHR(10)' 替换为更传统的“字母”字符证明 REPLACE 函数可以正常工作。我们还发现
REPLACE( col_name, CHR(10), '_' )
finds the locationof the new-line, but insertsthe underscore after it, rather than replacing it.
找到换行符的位置，但在它后面插入下划线，而不是替换它。
Running on Oracle8i. Upgrading is not an option.
在 Oracle8i 上运行。升级不是一种选择。
回答by Tony Andrews
回答by Martin Cowie
Ahah! Cade is on the money.
啊啊！凯德很有钱。
An artifact in TOAD prints \r\nas two placeholder 'blob' characters, but prints a single \ralso as two placeholders. The 1st step toward a solution is to use ..
TOAD 中的工件打印\r\n为两个占位符“blob”字符，但\r也将单个字符打印为两个占位符。解决方案的第一步是使用..
REPLACE( col_name, CHR(13) || CHR(10) )
.. but I opted for the slightly more robust ..
..但我选择了稍微更强大的..
REPLACE(REPLACE( col_name, CHR(10) ), CHR(13) )
.. which catches offending characters in any order. My many thanks to Cade.
.. 以任何顺序捕获违规字符。我非常感谢凯德。
M.
M。
回答by Cade Roux
Are you sure your newline is not CHR(13) + CHR(10), in which case, you are ending up with CHR(13) + '_', which might still look like a newline?
你确定你的换行符不是CHR(13) + CHR(10)，在这种情况下，你最终得到的是CHR(13) + '_'，它可能仍然看起来像一个换行符？
Try REPLACE(col_name, CHR(13) + CHR(10), '')
尝试 REPLACE(col_name, CHR(13) + CHR(10), '')
回答by Gordon Bell
If the data in your database is POSTED from HTML form TextArea controls, different browsers use different New Line characters:
如果数据库中的数据是从 HTML 表单 TextArea 控件 POSTED 的，则不同的浏览器使用不同的换行符：
Firefox separates lines with CHR(10) only
Firefox 仅使用 CHR(10) 分隔行
Internet Explorer separates lines with CHR(13) + CHR(10)
Internet Explorer 用 CHR(13) + CHR(10) 分隔行
Apple (pre-OSX) separates lines with CHR(13) only
Apple（OSX 之前）仅使用 CHR(13) 分隔行
So you may need something like:
所以你可能需要这样的东西：
set col_name = replace(replace(col_name, CHR(13), ''), CHR(10), '')
回答by Chris Brown
Just wanted to drop a note. I was having trouble formatting a text 4000 field that had a mind of its own and the text would seeming wrap (or not wrap) randomly on the report. When I updated the column using the replace chr(10) noted above. My report finally formatted as I wanted. Many Thanx!
只是想放下笔记。我在格式化一个有自己想法的文本 4000 字段时遇到问题，并且文本在报告中似乎随机换行（或不换行）。当我使用上面提到的替换 chr(10) 更新列时。我的报告终于按我的意愿格式化了。多谢！
回答by SQB
If your newline character is CRLF, that means it's a CHR(13)followed by CHR(10). If you REPLACE(input, CHR(10), '_'), that turns into CHR(13)followed by an underscore. Since CRon its own can be just as well rendered as a newline character, it'll appearto you as if an underscore has ben inserted after your newline, but actuallyonly half of your newline has been replaced.
如果您的换行符是CRLF，则表示它是CHR(13)后跟CHR(10). 如果您REPLACE(input, CHR(10), '_')，则变为CHR(13)后跟下划线。由于CR它本身可以像换行符一样呈现，因此在您看来，好像在换行符之后插入了下划线，但实际上只有一半的换行符已被替换。
Use REPLACE(REPLACE(input, CHR(13)), CHR(10))to replace all CR's and LF's.
使用REPLACE(REPLACE(input, CHR(13)), CHR(10))替换所有CR的和LF的。
