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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle