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

database oracle replace nclob
MrBoJangles picture MrBoJangles · Jan 27, 2012 · Viewed 12.7k times · Source

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!

Answer

MrBoJangles picture MrBoJangles · Jan 27, 2012

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

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How can I get column names from a table in Oracle?

I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would …

Read More