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
--
View this message in context:
http://www.nabble.com/Phone-number-formatting...-tp16019958p16491539.html
Sent from the ARS (Action Request System) mailing list archive at Nabble.com.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
