Oracle SQL – Remove Trailing Zeroes From Phone Columns

 

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;

 

Appreciate your feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: