Joe, You do know that you can use the TRANSLATE to drop characters as well (so you don't need to use REPLACE). If there is no matching character to translate to Oracle will drop that character off of the output completely. You can also use any Oracle function inside of a Set Fields SQL action. So to keep in line with the internal policies you can have a Filter on Submit and Modify do a Set Fields SQL action to clean up the fields. Fred
________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza Sent: Thursday, April 03, 2008 4:44 PM To: arslist@ARSLIST.ORG Subject: Re: Phone number formatting... ** 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(rep lace(replace(replace(replace(replace(replace(replace(PHONE_HOME,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^','') ) as PHONE_HOME, trim(replace(translate(replace(replace(replace(replace(replace(upper(rep lace(replace(replace(replace(replace(replace(replace(replace(replace(PHO NE_BUSINESS,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' X','?'),' EXT','?'),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^','') ) as PHONE_BUSINESS, trim(replace(replace(replace(replace(translate(replace(replace(replace(u pper(replace(replace(replace(replace(replace(replace(replace(replace(rep lace(PHONE_PAGER,'+',''),'/',''),'-',''),'(',''),')',''),'!',''),'#','') ,'*',''),',','')),'.',' '),' ',' '),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?','') ,'@',''),':',''),'\','')) as PHONE_PAGER, trim(replace(replace(replace(replace(translate(replace(replace(replace(u pper(replace(replace(replace(replace(replace(replace(replace(replace(rep lace(PHONE_PAGER_2,'+',''),'/',''),'-',''),'(',''),')',''),'!',''),'#',' '),'*',''),',','')),'.',' '),' ',' '),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?','') ,'@',''),':',''),'\','')) as PHONE_PAGER_2, trim(replace(translate(replace(replace(replace(upper(replace(replace(rep lace(replace(replace(replace(replace(replace(replace(PHONE_CELL,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' ',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','??????????????????????????'),'?','') ) as PHONE_CELL, trim(replace(translate(replace(replace(replace(replace(replace(upper(rep lace(replace(replace(replace(replace(replace(replace(replace(replace(PHO NE_FAX,'+',''), '/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),' ',' '),' OR ','/'),' ',''),'7ST',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','?????????????????????????? '),'?','')) as PHONE_FAX, trim(replace(translate(replace(replace(replace(replace(replace(replace(u pper(replace(replace(replace(replace(replace(replace(replace(replace(rep lace(PHONE_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((re place((replace(z_phone_altb,'+','')), '/','')),'-','')),'(','')),')','')),'!','')),'#','')),'*','')),',','')) as Width, replace((replace((replace((replace((replace((replace((replace((replace(( replace(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 __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"
