Not sure if you got what you needed yet, but here's a straight sql query to
do what you are asking:
Joe D'Souza 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
>
> Think of any permutation of text and number you will find it in this
> database as its virtually free format character field
>
> My aim with a select statement is to get it clean enough and remove all
> the
> bold part to make it look like
> 7777777777 x 7777
> 7777777777 x 7777
> 7777777777 x 7777
> 7777777777 x 7777
> 7777777777 x 7777
> 7777777777 x 7777
>
> This is the SQL statement I wrote that got rid of most of the stuff BUT I
> am
> still wondering how I would able to exclude all alphabets with a single
> replace added to the already existing nest.. while converting every
> possible
> combination of ext Ext EXT etc to x..
>
> So far I have written
> select
> length(replace((replace((replace((replace((replace((replace((replace((replac
> e((replace(z_phone_altb,'+','')),
> '/','')),'-','')),'(','')),')','')),'!','')),'#','')),'*','')),',','')) as
> Width,
> replace((replace((replace((replace((replace((replace((replace((replace((repl
> ace(z_phone_altb,'+','')),
> '/','')),'-','')),'(','')),')','')),'!','')),'#','')),'*','')),',','') as
> Phone_altb from ps_z_****** order by Width;
>
> (I was interested in width to see if there may be some numbers in a
> possible
> incorrect format (less than 10 or greater than 10 for USA and so on)
>
> This is the typical output I got from the above statement..
> 777.777.7777 ext 7777
> 7777777777 Ext.7777
> 777777.7777 X 7777
> 777.777.7777 janes number
> my number 7777777777 x7777
> 7777777777 this is my number
>
> I can remove the dots, no problem.. even did that, but then thought they
> might be useful till I took off all the Ext. or x. or ext. from the data
> and
> replaced that with x.. How do I get rid of all the other random alphabets
> while keeping the ext?? I thought of replacing ext with ?? but is there a
> single replace that I can write to get rid of everything in the range a to
> z
> and A to Z??
>
> Any ideas?
>
> Joe D'Souza
> 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"
>
>
http://www.nabble.com/file/p16429225/BadPhoneNbrs.sql BadPhoneNbrs.sql
--
View this message in context:
http://www.nabble.com/Phone-number-formatting...-tp16019958p16429225.html
Sent from the ARS (Action Request System) mailing list archive at Nabble.com.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
