I need to stick to SQL as I am using AIE to interface to PeopleSoft so writing the details of the whole PeopleSoft view to a text file and having AIE to read from that will not be really that much cost effective.. it will involve time consuming step.. I wanted to design the replace part of the statement using SQL so that I can translate the same to AIE..
As it is after that I further need to break the output I get into the area code and local number and extension or pins if any.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Kurniadi Sent: Wednesday, March 12, 2008 11:23 PM To: arslist@ARSLIST.ORG Subject: Re: Phone number formatting... Hi Joe, how about export the data and using text editor with regular expression feature to clean it up. I use Notepad++ (it's open source) Some regexes to clean the data - replace all format of Ext and x to unique string replace: [Ee][Xx][Tt]\.* replace: x - replace non digit replace: [^1-9] - clean alphabets replace: [A-Za-z] Regex for Notepad++ http://notepad-plus.sourceforge.net/uk/regExpList.php Kur Niadi On 3/13/08, Joe D'Souza <[EMAIL PROTECTED]> wrote: > ** > In the intention to clean up the phone number information that is coming > from a PeopleSoft database that has phone numbers in a practically free > format I am trying to write a select statement so that all non numeric > characters in the field will be eliminated. > > A sample of the phone data could be > ###777.777.7777 ext 7777 > 777/777-7777 Ext.7777 > 777-777.7777 X 7777/////// > 777.777.7777 janes number!!!!! > my number## 7777777777 x7777 > +7777777777 ,this is my number No virus found in this outgoing message. Checked by AVG. Version: 7.5.518 / Virus Database: 269.21.7/1327 - Release Date: 3/12/2008 1:27 PM _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
