In my case, I was able to do all of this with Filters, but the difference is that I have to deal only with U.S.-based numbers. The first filter to execute strips out all characters, and the second filter to execute sets the length of the remaining numerical data, then based on how many numbers I have, I split it out appropriately before pushing it into the real form.
If I had to work with many international numbers, my first step would be to see if there is a web service that can translate the format for me. Thanks, Shawn Pierson From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza Sent: Friday, April 04, 2008 9:27 AM To: [email protected] Subject: Re: Phone number formatting... ** Most applications default phone number columns to characters instead of number.. This is to allow flexibility for users to put special instructions such as Extension or pin numbers texts.. What many applications don't do however is control the usage of this field. IMHO, the data in these fields would have been a lot more cleaner if the input was controlled, meaning at the application level there were fields such as country codes, area codes, local numbers and extensions that allowed input of digits only. Remedy ITSM applications now have that but they do not really control the input into these fields to be digits only.. so basically if the user uses them correctly, you do get a clean format, else junk.. What I wrote would be confusing in case I need to modify it, but for simplicity I have broken it down with more carriage returns than you see in my posting, so it gets easier to read. I'll have a look at your query too and maybe could use it the next time I have to do something similar.. I do however understand the idea on which you based your query and its a interesting thought considering PS phone number fields are 24 characters in length. The only drawback I can think of is that you will need to modify yours, if in case the length of the field is modified for whatever reasons... In my case that wouldn't need a modification, but may need one if we find more special characters in the user input somewhere down the line.. I'm already way too deep into mine to revert at this phase! Thanks for your suggestions.. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of PS_Is_Fun Sent: Friday, April 04, 2008 9:52 AM To: [email protected] Subject: Re: Phone number formatting... That does look like it'll work, but so many Replaces can get confusing. Here's the sql I have and it works great. You can change the formatting you want using the Substring function after stripping the field. Only downfall of this one is that you have to do a case statement for each character in a string. Our Phone field is 24 chars, Address field is usually a bit longer, but it still works. Don't know why PeopleSoft defaults the Phone field to allow any chars. I've been working with the Field Format property to create our own custom restrictions. It's pretty helpful to prevent future wacky input after you clean up your data. (if that's your intention). Happy Querying! SELECT A.EMPLID, A.PHONE_TYPE, A.PHONE as PHONE_ORIG_FORMAT, A.STRIPPED_PHONE, '(' + SUBSTRING(RTRIM(A.STRIPPED_PHONE), 1, 3) + ') ' + SUBSTRING(RTRIM(A.STRIPPED_PHONE), 4, 3) + '-' + SUBSTRING(RTRIM(A.STRIPPED_PHONE), 7, 4) + ' X ' + SUBSTRING(RTRIM(A.STRIPPED_PHONE), 11, LEN(RTRIM(A.STRIPPED_PHONE))) AS FORMATTED_PHONE, SUBSTRING(RTRIM(A.STRIPPED_PHONE), 1, 10) + ' X ' + SUBSTRING(RTRIM(A.STRIPPED_PHONE), 11, LEN(RTRIM(A.STRIPPED_PHONE))) AS FORMATTED_PHONE2 FROM (select EMPLID, PHONE_TYPE, PHONE, case when substring( PHONE , 1 , 1 ) LIKE '[0-9]' then substring( PHONE , 1 , 1 ) else '' end + case when substring( PHONE , 2 , 1 ) LIKE '[0-9]' then substring( PHONE , 2 , 1 ) else '' end + case when substring( PHONE , 3 , 1 ) LIKE '[0-9]' then substring( PHONE , 3 , 1 ) else '' end + case when substring( PHONE , 4 , 1 ) LIKE '[0-9]' then substring( PHONE , 4 , 1 ) else '' end + case when substring( PHONE , 5 , 1 ) LIKE '[0-9]' then substring( PHONE , 5 , 1 ) else '' end + case when substring( PHONE , 6 , 1 ) LIKE '[0-9]' then substring( PHONE , 6 , 1 ) else '' end + case when substring( PHONE , 7 , 1 ) LIKE '[0-9]' then substring( PHONE , 7 , 1 ) else '' end + case when substring( PHONE , 8 , 1 ) LIKE '[0-9]' then substring( PHONE , 8 , 1 ) else '' end + case when substring( PHONE , 9 , 1 ) LIKE '[0-9]' then substring( PHONE , 9 , 1 ) else '' end + case when substring( PHONE , 10 , 1 ) LIKE '[0-9]' then substring( PHONE , 10 , 1 ) else '' end + case when substring( PHONE , 11 , 1 ) LIKE '[0-9]' then substring( PHONE , 11 , 1 ) else '' end + case when substring( PHONE , 12 , 1 ) LIKE '[0-9]' then substring( PHONE , 12 , 1 ) else '' end + case when substring( PHONE , 13 , 1 ) LIKE '[0-9]' then substring( PHONE , 13 , 1 ) else '' end + case when substring( PHONE , 14 , 1 ) LIKE '[0-9]' then substring( PHONE , 14 , 1 ) else '' end + case when substring( PHONE , 15 , 1 ) LIKE '[0-9]' then substring( PHONE , 15 , 1 ) else '' end + case when substring( PHONE , 16 , 1 ) LIKE '[0-9]' then substring( PHONE , 16 , 1 ) else '' end + case when substring( PHONE , 17 , 1 ) LIKE '[0-9]' then substring( PHONE , 17 , 1 ) else '' end + case when substring( PHONE , 18 , 1 ) LIKE '[0-9]' then substring( PHONE , 18 , 1 ) else '' end + case when substring( PHONE , 19 , 1 ) LIKE '[0-9]' then substring( PHONE , 19 , 1 ) else '' end + case when substring( PHONE , 20 , 1 ) LIKE '[0-9]' then substring( PHONE , 20 , 1 ) else '' end + case when substring( PHONE , 21 , 1 ) LIKE '[0-9]' then substring( PHONE , 21 , 1 ) else '' end + case when substring( PHONE , 22 , 1 ) LIKE '[0-9]' then substring( PHONE , 22 , 1 ) else '' end + case when substring( PHONE , 23 , 1 ) LIKE '[0-9]' then substring( PHONE , 23 , 1 ) else '' end + case when substring( PHONE , 24 , 1 ) LIKE '[0-9]' then substring( PHONE , 24 , 1 ) else '' end AS STRIPPED_PHONE FROM PS_PERSONAL_PHONE GROUP BY EMPLID, PHONE_TYPE, PHONE HAVING RTRIM(PHONE) <> '')A WHERE LEN(RTRIM(A.STRIPPED_PHONE)) >= 10 __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ Private and confidential as detailed here: http://www.sug.com/disclaimers/default.htm#Mail . If you cannot access the link, please e-mail sender. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
