Posts Tagged ‘Remove’

Oracle SQL – Remove Trailing Zeroes From Phone Columns



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.



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



I put up three in-built SQL library functions to use as follow:

  PC.WORK_PH_NUM “Full WorkPhone#”,
  Case When INSTR(PC.WORK_PH_NUM,CHR(10)) > 0
     Else PC.WORK_PH_NUM
  End “Updated WorkPhone#”
From Siebel.S_Contact PC;


Trim leading or Trailing Spaces in Siebel eScript



 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

  • 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


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 for additional information. 

Happy Scipting. 🙂