I didn't get what you were trying to send but, I did work out a pretty good
query that cleaned up the results on a view form, but they are not willing
to accept that solution as its a view that is build from the Oracle
database, so outside the application interface.. they are looking for a
solution built fully using the application interface.. Translate was the
Oracle function I was looking for, in combination with another replace, to
complete the rest to what I had posted i.e. to replace all free text
alphabets with null...
This is the query I wrote that formatted phone numbers as well as Address
related fields..
Create or Replace view PSI_PSDC_VW_IBMCNA as
Select REQUEST_ID, SUBMITTER, CREATE_DATE, ASSIGNED_TO, LAST_MODIFIED_BY,
MODIFIED_DATE, STATUS, SHORT_DESCRIPTION, EMPL_ID, ALTER_EMPL_ID, NAME,
FIRST_NAME, MIDDLE_NAME, LAST_NAME, PERSON_TYPE, PER_ORG, DESCR,
CN_REPORTS_TO_ID, MANAGER_NAME, ACCT_CD, COST_CENTER_MANAGER, PAY_GROUP,
JOB_CODE, JOB_CODE_DESCRIPTION, LOCATION, DEPT_ID,
trim(replace(translate(replace(replace(replace(upper(replace(replace(replace
(replace(replace(replace(replace(replace(replace(PHONE_HOME,'+',''),
'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),'
',' '),'
',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^','')) as
PHONE_HOME,
trim(replace(translate(replace(replace(replace(replace(replace(upper(replace
(replace(replace(replace(replace(replace(replace(replace(replace(PHONE_BUSIN
ESS,'+',''),
'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),'
',' '),' X','?'),' EXT','?'),'
',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^','')) as
PHONE_BUSINESS,
trim(replace(replace(replace(replace(translate(replace(replace(replace(upper
(replace(replace(replace(replace(replace(replace(replace(replace(replace(PHO
NE_PAGER,'+',''),'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),','
,'')),'.',' '),' ',' '),'
',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?',''),'@'
,''),':',''),'\','')) as PHONE_PAGER,
trim(replace(replace(replace(replace(translate(replace(replace(replace(upper
(replace(replace(replace(replace(replace(replace(replace(replace(replace(PHO
NE_PAGER_2,'+',''),'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),'
,','')),'.',' '),' ',' '),'
',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?',''),'@'
,''),':',''),'\','')) as PHONE_PAGER_2,
trim(replace(translate(replace(replace(replace(upper(replace(replace(replace
(replace(replace(replace(replace(replace(replace(PHONE_CELL,'+',''),
'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),'
',' '),'
',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?','')) as
PHONE_CELL,
trim(replace(translate(replace(replace(replace(replace(replace(upper(replace
(replace(replace(replace(replace(replace(replace(replace(replace(PHONE_FAX,'
+',''),
'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),'
',' '),' OR ','/'),'
',''),'7ST',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'
?','')) as PHONE_FAX,
trim(replace(translate(replace(replace(replace(replace(replace(replace(upper
(replace(replace(replace(replace(replace(replace(replace(replace(replace(PHO
NE_ALTERNATE_BUS,'+',''),
'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),'
',' '),' X','?'),' EXT','?'),'
',''),'PIN','?'),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),
'^','')) as PHONE_ALTERNATE_BUS,
EMPLOYEE_STATUS, GRADE, COMPANY, L2_DEPT_ID, L2_DESCR, MANAGER_POSITION,
EMAIL_BUSINESS, POSITION_NUMBER, EMAIL_HOME, MANAGER_ID, REG_REGION,
trim(replace(replace(replace(ADDRESS_1,'|',''),'~',''),' ',' ')) as
ADDRESS_1,
trim(replace(replace(replace(ADDRESS_2,'|',''),'~',''),' ',' ')) as
ADDRESS_2,
ADDRESS_3, ADDRESS_4, COUNTRY, STATE,
trim(replace(replace(replace(CITY,'|',''),'~',''),' ',' ')) as CITY,
trim(replace(replace(replace(POSTAL,'|',''),'~',''),' ',' ')) as POSTAL,
SECTOR,
trim(replace(replace(replace(ADDRESS_1_2,'|',''),'~',''),' ',' ')) as
ADDRESS_1_2,
trim(replace(replace(replace(ADDRESS_2_2,'|',''),'~',''),' ',' ')) as
ADDRESS_2_2,
ADDRESS_3_2, ADDRESS_4_PS, COUNTRY_2, STATE_2, CITY_2, POSTAL_2,
HOURLY_RATE, LEVEL_NUMBER, PERSON_ID, AREA_BUSINESS, AREA_BUSINESS_FAX,
AREA_HOME, AREA_MOBILE, AREA_PAGER, AREA_PAGER_NT, CC_BUSINESS,
CC_BUSINESS_FAX, CC_HOME, CC_MOBILE, CC_PAGER, CC_PAGER_NT,
EXTENSION_BUSINESS, LOCAL_BUSINESS, LOCAL_BUSINESS_FAX, LOCAL_HOME,
LOCAL_MOBILE, LOCAL_PAGER, LOCAL_PAGER_NT, PIN_PAGER_NT, PIN_PAGER
from PSI_PeopleSoftDataContainer_IB;
I got phone numbers with extensions in the format 7777777777?7777 where ?
represents that numbers following it are extensions.
It works but unfortunately I cannot use it because of some of their internal
policies there. Well at least I had some joy writing the above :-)
Cheers
Joe
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] Behalf Of PS_Is_Fun
Sent: Thursday, April 03, 2008 4:55 PM
To: arslist@ARSLIST.ORG
Subject: Re: Phone number formatting...
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.519 / Virus Database: 269.22.5/1358 - Release Date: 4/3/2008
6:36 PM
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
