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: arslist@ARSLIST.ORG 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 No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.5/1359 - Release Date: 4/4/2008 8:23 AM _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
