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

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.

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!

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.

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!

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.

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

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 CR on its own can be just as well rendered as a newline character, it'll appear to you as if an underscore has ben inserted after your newline, but actually only half of your newline has been replaced.

Use REPLACE(REPLACE(input, CHR(13)), CHR(10)) to replace all CR's and LF's.

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

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

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

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

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

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.

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

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 CR on its own can be just as well rendered as a newline character, it'll appear to you as if an underscore has ben inserted after your newline, but actually only half of your newline has been replaced.

Use REPLACE(REPLACE(input, CHR(13)), CHR(10)) to replace all CR's and LF's.

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

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.