2

We have information in an Oracle db of type NCLOB, and I want to remove newline characters. This, for example, does not work:

MyNclobCell := REPLACE(MyNclobCell, '\n', '');

Do I have an answer below? Yes, yes I do!

MrBoJangles
  • 12,127
  • 17
  • 61
  • 79

1 Answers1

8

Turns out that I needed to remove both the newline (\n, ascii: 10) and the carriage return (\r, ascii: 13) characters. To use my example above, the one line of code became two as follows:

MyNclobCell := REPLACE(MyNclobCell, chr(10), '');
MyNclobCell := REPLACE(MyNclobCell, chr(13), '');

I then got my NCLOB rows all in a single line, and pasted the contents into a spreadsheet and handed it off to the requestor, hooray!

Update: As per Saurabh Patil's suggestion, an alternative syntax for code-golfers and single-line-likers:

REPLACE(REPLACE(MyNclobCell, chr(10), ''), chr(13), '');
Community
  • 1
  • 1
MrBoJangles
  • 12,127
  • 17
  • 61
  • 79
  • Ok, then. This'll be the accepted answer unless someone has something better. – MrBoJangles Feb 08 '12 at 17:00
  • 2
    To use it in the query you can write it as: REPLACE(REPLACE(MyNclobCell, chr(10), ''), chr(13), ''); – Saurabh Patil Nov 20 '13 at 00:15
  • if the field has like below " line -1 has ABC and next line empty and then line 3 has DEF" ...the above command makes ABCDEF and how I can make as ABC DEF or ABC,DEF ? – kanagaraj Aug 07 '19 at 18:49
  • @kanagaraj looks like a question worthy of it's own post. – MrBoJangles Aug 16 '19 at 19:40
  • 2
    @Jangles thanks for reply , I got solution for this issue and the below has worked REGEXP_REPLACE(REPLACE(REPLACE(NOTES, CHR(10), ';'), CHR(13), ';'), '(;){2,}', ';')) – kanagaraj Aug 19 '19 at 15:19