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"
