Never really thought of that.. so didn't look to see if there were any
available web services that would do what I want..
I was trying to use the following statement in a direct SQL, and instead of
just one $1$ column, I get 4, $1$, $2$, $3$ and $4$ as available outputs to
choose from.. Needless to say these are wrong.. so where am I going wrong in
this statement? Does ARS think a space is a separation character to
represent another column???
I'm using the following in my Direct SQL statement in my set field action...
Select
trim(replace(translate(replace(replace(replace(replace(replace(upper(replace
(replace(replace(replace(replace(replace(replace(replace(replace($Phone
Business$,'+',''),
'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),'
',' '),' X','?'),' EXT','?'),'
',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^',''))
from dual
Thinking that it may need a single quote around the Phone Business field I
even tried the below with the same results..
Select
trim(replace(translate(replace(replace(replace(replace(replace(upper(replace
(replace(replace(replace(replace(replace(replace(replace(replace('$Phone
Business$','+',''),'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),'
,','')),'.',' '),' ',' '),' X','?'),' EXT','?'),'
',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','^^^^^^^^^^^^^^^^^^^^^^^^^^'),'^',''))
from dual
So where I going wrong?
Joe
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] Behalf Of Pierson, Shawn
Sent: Friday, April 04, 2008 10:48 AM
To: arslist@ARSLIST.ORG
Subject: Re: Phone number formatting...
**
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: arslist@ARSLIST.ORG
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: 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"
