pl/sql 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.

Clues most welcome.

Answer 1

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?

Try REPLACE(col_name, CHR(13) + CHR(10), '')

Answer 2

If the data in your database is POSTED from HTML form TextArea controls, different browsers use different New Line characters:

Firefox separates lines with CHR(10) only

Internet Explorer separates lines with CHR(13) + CHR(10)

Apple (pre-OSX) separates lines with CHR(13) only

So you may need something like:

set col_name = replace(replace(col_name, CHR(13), ''), CHR(10), '')

Answer 3

Ahah! Cade is on the money.

An artifact in TOAD prints \r\n as two placeholder 'blob' characters, but prints a single \r also as two placeholders. The 1st step toward a solution is to use ..

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.

Answer 4

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.

Answer 5

Thx Martin and Cade, I needed your solution to get the correct Excel output from my concurrent requests (generated by XML Publisher)! Now I don't have split Excel cells =)

Answer 6

Thank you Gordon that worked for me

Answer 7

Thanks Gordon, appreciate your help