databaseoracle-databasereplacenclob

Oracle nclob fields: remove newline (or carriage return) characters

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!

Solution

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