Joe, I would look toward the Java Scripting Plugin. (Call a Filter Plugin and use RegEx's from there.) That approach will keep the work in the Application layer and also keep it RDBMS independent.
Just my two cents. -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Love, then teach Solution = People + Process + Tools Fast, Accurate, Cheap.... Pick two. On Wed, Mar 12, 2008 at 10:38 PM, Joe D'Souza <[EMAIL PROTECTED]> wrote: > ** > > 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: [email protected] > 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 __Platinum Sponsor: www.rmsportal.com > ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
