sql - Oracle REPLACE() function isn't handling carriage-returns & line-feeds

Question

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

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.

Running on Oracle8i. Upgrading is not an option.

Answer 1

Another way is to use TRANSLATE:

TRANSLATE (col_name, 'x'||CHR(10)||CHR(13), 'x')

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.