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(replace
(replace(replace(replace(replace(replace(replace(replace(replace(PHONE_BUSIN
ESS,'+',''),
'/',''),'-',''),'(',''),')',''),'!',''),'#',''),'*',''),',','')),'.',' '),'
',' '),' 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
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1348 - Release Date: 3/28/2008
10:58 AM
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
