You are correct ... that is extremely nasty
If you were allowed to use an Oracle (asuming you are on oracle) SQL Set
Fields action you could do it like
SELECT TRANSLATE( REPLACE( TRANSLATE( UPPER('$PHONE_BUSINESS$'),
'+/-()!#*,.', ''), 'EXT', '?'), 'XABCDEFGHIGKLMNOPQRSTUVWYZ', '?') FROM
dual
No need for any type of view and all of it is contained inside Remedy.
Fred
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza
Sent: Friday, March 28, 2008 6:33 PM
To: [email protected]
Subject: Re: Equivalent of the TRANSLATE function in Remedy
**
Rick,
Not sure if you can use the ASCII range, never tried it..
Thad,
Yes same phone number issue and while doing it directly at SQL yes I did
use Upper before Translate so I have to translate less...
The actual select statement that I designed, as a result of all the
formatting that was necessary, was too long and may not be fun for some
of you to read on a day when Friday Humor is posted, so I'll copy how I
read one of the phone numbers while creating a db view instead of the
whole SQL statement I wrote..
Unfortunately the customer wants nothing to do with external db views or
direct SQL to the view, so I cannot use it but need to translate the SQL
below to ARS code.. While I am good with translating TRIM to LTRIM and
RTRIM and REPLACE and UPPER to REPLACE and UPPER in Remedy, my only
option seems to be a nested replace 26 times for alpha characters
instead of a single TRANSLATE..
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,
The above reads the Phone_Business column in the external view after
stripping it off all the junk, and replacing " EXT" or " X" for
extension with a "?".. That delimit of "?" would tell me if a phone
number has an extension and where the extension string began from.. This
replacement is done before the translation of alpha characters to ^ so
we do not loose the extension information - I have taken care of that!
So the end result is a numeric character string.
I created a view with the above statement applied to most phone number
columns during the view creation and got a clean view without loosing
extension information in any of the records.. (32101 records) So far I
was happy except that now it looks like to translate the above statement
to Remedy code might be a more expensive process considering that Remedy
doesn't have a TRANSLATE function..
I wonder if cases like this would qualify for an RFE.. TRANSLATE is used
by all RDBMS's right??? I know it is used in MS-SQL too.. not so sure
about My-SQL and Informix..
Joe
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] Behalf Of Thad K Esser
Sent: Friday, March 28, 2008 6:53 PM
To: [email protected]
Subject: Re: Equivalent of the TRANSLATE function in Remedy
**
Joe,
Sorry, I'm not sure there's an easy answer to this one without
using the SQL. I think you'll have to loop through the string one
character at a time to doing your find and replace. Some thoughts
though:
1. Does this have to do with your phone number formatting
issue? Would it be easier to approach the problem by looking to include
valid values (numbers) versus replacing non-valid values (alpha)?
2. If you have to create a nested replace for all alphabetic
characters, since you're going to remove them anyway, use an UPPER() or
LOWER() on the string first, so you only have to do half as many
replacements.
3. Is the server on Unix? Could you use a unix Translate
command ( http://en.wikipedia.org/wiki/Tr_(Unix) ) in a run process
instead of SQL? (Oh hell, I just looked it up, of course the command is
"tr". I can't believe I have to suggest that. :-)
Anyway, I hope that helps some.
Thad Esser
Remedy Developer
"Argue for your limitations, and sure enough, they're yours."--
Richard Bach
"Joe D'Souza" <[EMAIL PROTECTED]>
Sent by: "Action Request System discussion list(ARSList)"
<[email protected]>
03/28/2008 03:00 PM
Please respond to
[email protected]
To
[email protected]
cc
Subject
Equivalent of the TRANSLATE function in Remedy
**
Has anyone implemented the equivalent of the Oracle TRANSLATE
function in Remedy?
I have a field where I wish to translate all the alpha
characters into lets say ^ and then replace ^ with null to make that
field free of alpha characters.
The only way I can think it is possible right now is to have a
nested REPLACE for each character. Any other method?
I have been asked to avoid direct SQL's as far as I can so I'm
going to resort to Direct SQL as a last resort option..
Thanks Thad for responding to that one.. Maybe you know the
answer to this one too :-). Unfortunately on this gig I do not have that
much of a luxury to 'try' it out'
Cheers
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"
