Posts Tagged ‘Remove’

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;

 

Trim leading or Trailing Spaces in Siebel eScript

 

Scenario

 As there is no library function available for trimming leading or trailing characters (like spaces) in Siebel eScript, one need to create a custom function. Following options can be used:

  • Traditional approach – Using While/Recursion
  • Optimized approach – Using Regular Expressions

One can easily design the function with while/recursion, so I won’t discuss that. I will start with the Second approach.

 

Approach 2 : Using Reg Expr

As Siebel escript supports Regular expressions, I will show you how to trim the characters using them:

  • RTrimSpaces (OrgStr) – This function will remove the trailing spaces from the passed string.

function RTrimSpaces (OrgStr)
{
 var Pattern = /\s*$/; //Regular Expression
 var TrimStr = OrgStr.replace(Pattern,””); //Replace with blank
    
 return(TrimStr);
}

  • LTrimSpaces (OrgStr) – This function will remove the leading spaces.

function LTrimSpaces (OrgStr)
{
 var Pattern = /^\s*/; //Regular Expression
 var TrimStr = OrgStr.replace(Pattern,””); //Replace with blank
    
 return(TrimStr);

 

Understanding Reg Expr Used

 Let me explain you elements of the Regular Expressions used in above functions:

  • ‘/’ – Marks the start and end of a pattern
  • ‘\s’ – Matches the whitespace characters like space, tab, new line
  • ‘^’ – Instruct to search at the begining of the string
  • ‘$’ – Instruct to search at the end of the string
  • ‘*’ – Instruct to find zero or more instances

I have used Reg Expr in a specific scenario. But if you want to play around with it, please refer to http://gskinner.com/RegExr/ for additional information. 

Happy Scipting. 🙂