Recently, I was approached by one of my customer’s dba team. They were migrating data from their production database running on Oracle DBMS to a Tera data database system. One column of type VARCHAR2 from the source table had multiple end of line and carriage return characters because that column was related to a comment field on the front end form. Tera data was not able to handle these special characters and was failing the import process.

I suggested to use the standard SQL function replace to replace the end of line and carriage return characters to none while selecting data using SQL.

For example, Column col1 of tab1 has following value from the source database:

This is a sample string because I can not share customer’s production data.

As you can see this is another new line, which is right because end user can have multiple line in there comments.

As stated above, the target database was not handling this multiple line value correctly hence replace function was used toreplace the end of line and carriage return characters with no value.