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 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
