Oracle REPLACE()函数无法处理回车符&换行 [英] Oracle REPLACE() function isn't handling carriage-returns & line-feeds

问题描述

我们有一个带有varchar2(100)列的表，该表偶尔包含回车符&换行.我们想在SQL查询中删除那些字符.我们正在使用:

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:

REPLACE( col_name, CHR(10) ) 

无效，但是将"CHR(10)"替换为更常规的字母"字符证明了REPLACE函数可以正常工作.我们还发现

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

REPLACE( col_name, CHR(10), '_' ) 

找到换行的位置，但在下划线后插入，而不是替换下划线.

finds the location of the new-line, but inserts the underscore after it, rather than replacing it.

在Oracle8i上运行.不能升级.

Running on Oracle8i. Upgrading is not an option.

推荐答案

另一种方法是使用"x"是您不希望转换为null的任何字符，因为如果第3个参数为null，TRANSLATE将无法正常工作.

The 'x' is any character that you don't want translated to null, because TRANSLATE doesn't work right if the 3rd parameter is null.

这篇关于Oracle REPLACE()函数无法处理回车符&换行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！