How to replace a character programatically in Oracle 8.x series

Question

Due to repetitive errors with one of our Java applications:

Engine engine_0: Error in application action.
org.xml.sax.SAXParseException: An invalid XML character (Unicode: 0x13)
was found in the element content of the document.

I need to "fix" some Unicode character in an Oracle database, ideally in a programmatic fashion. Once identified, what would be a simple way to "search and replace" it?

Answer 1

Assuming the characters are present in a text field:

update TABLE set COLUMN=REPLACE(convert(varchar(5000), COLUMN), 'searchstring', 'replacestring')

(note that this will only work on a text field with no more than 5000 characters, for larger text fields increase the number in the query).