I had a requirement to take extract of all Contacts from Siebel Database (Oracle Database in this case). I designed the SQL including all necessary columns and took the extract in CSV format.
There were two challenges the I faced:
- Linebreaks in the phone fields like Work Phone#, Fax Phone#, Home Phone#.
- Linebreaks in the comments fields.
Due to this reasons, a single contact record was getting scattered to more than two lines in text file. This led to inconsistency.
Replace Function was used to resolve it. The syntax is:
replace( string1, string_to_replace, [ replacement_string ] )
where, string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced by it in string1.
Following SQL was used:
, REPLACE(Con.FAX_PH_NUM, CHR(10), ‘ ‘) as “Updated_Fax”
, REPLACE(Con.COMMENTS, CHR(13) || CHR(10), ‘ ‘) as “Updated_Comments”
from siebel.S_contact con;
Line Feed (LF) character (\n) has 10 as ASCII code, whereas Carriage Return (CR) character (\r) has 13 as ASCII code.
In Unix system, newline character is LF. But in Microsoft Windows system, CR+LF represents the newline character.