Today while I was doing consultancy work I faced against the issue of loading a table into from Oracle to PostgreSQL, when I checked the logs I saw the some oracle varchar fields had strange characters at the end of them and this caused INSERT statements fail. Initially I tried using Pentaho Data Integration replace values in string and replace CR, LF and CRLF since they looked like carriage returns when copied the log files in Notepad++. But all attempts were unsuccessful, so I decided to look for Oracle functions and soon I got a proper solution.
REGEXP_REPLACE helped my as you could see in the query below
SELECT REGEXP_REPLACE( customer_description ,'[^[:alnum:]'' '']', NULL) FROM dim_customer
The [[:alnum:]] character class represents alphabetic and numeric characters, and it is same as using [a-zA-Z0-9] in regular expression.
Hope you have enjoyed 🙂