Requirement
I designed the sql to extract Contacts from Siebel Database (Oracle Database in this case) including phone columns. The challenge that I faced with phone columns:
- Trailing zeroes (Used by Siebel to capture phone number format)
My extract was not supposed to contain them.
Concept
Siebel puts a Newline character between actual phone data and its format.
Also, in my case, Siebel database was installed on a Unix server. In Unix, Newline character is represented by LineFeed (LF) character (\n), Its ASCII code is 10.
Solution
I put up three in-built SQL library functions to use as follow:
Select
PC.WORK_PH_NUM “Full WorkPhone#”,
Case When INSTR(PC.WORK_PH_NUM,CHR(10)) > 0
Then SUBSTR(PC.WORK_PH_NUM,0,INSTR(PC.WORK_PH_NUM,CHR(10))-1)
Else PC.WORK_PH_NUM
End “Updated WorkPhone#”
From Siebel.S_Contact PC;
