29

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.

Martin Cowie
  • 2,788
  • 7
  • 38
  • 74

6 Answers6

56

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.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • Could you elaborate on the `'x'` portion of the code? I'm not following. I understand you need to specify a replacement, but why do you need to include the replacement at the beginning of the second argument? – Kevin Bowersox Apr 25 '13 at 11:56
  • 3
    If you write `TRANSLATE (col_name, CHR(10)||CHR(13), null)` then it will not work - it will always return NULL (a quirk of null handling). If you write `TRANSLATE (col_name, CHR(10)||CHR(13), 'x')` then it will convert each CHR(10) to 'x', which is not what is wanted. If we prefix both arguments with an 'x' then it will translate 'x' to 'x' and CHR(10), CHR(13) to null, giving the desired result. – Tony Andrews Apr 25 '13 at 12:52
  • Gotcha, so that is what this part of the reference means `In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed from the return value.` Thanks! – Kevin Bowersox Apr 25 '13 at 12:58
  • 1
    still valid and being used in SSIS 2019 for migration of data from Oracle to Snowflake ; 12 yrs from OP. Thanks for sharing this. – junketsu Apr 13 '21 at 14:14
33

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.

Martin Cowie
  • 2,788
  • 7
  • 38
  • 74
15

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

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • 1
    Thanks for this, it really helped me. I had to export some database content to an external system, using a CSV (I did not choose this...), and I wanted to convert carriage returns to a simple dot. Just wanted to add that I had a syntax error with the "+" sign and had to replace it with a "||" like this : `REPLACE(col_name, CHR(13) || CHR(10), '')` – Nicolas Vannier May 24 '17 at 10:42
7

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), '')
Gordon Bell
  • 13,337
  • 3
  • 45
  • 64
1

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!

Chris Brown
  • 11
  • 1
0

If your newline character is CRLF, that means it's 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 been 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.

SQB
  • 3,926
  • 2
  • 28
  • 49