Joe, do you want to remove text characters or do you want to keep numbers? tha'ts whitelisting vs blacklisting and will be faster.
Regards, Michiel On Sun, Mar 30, 2008 at 12:47 AM, Joe D'Souza <[EMAIL PROTECTED]> wrote: > ** Yes the thing Is I don't think I am allowed to use direct SQL.. I have > a statement ready for use like you suggested, plus I have a view ready with > clean data, but have not got approvals to use that. The thing is this > customer does not have an onsite Remedy admin and are unwilling at the > moment to use external means to drive their applications.. They are worried > about maintaining it after I leave even if I do leave them with SQL scripts > and instructions how to maintain the view using those .SQL files.. > > I 'might' just have to resort to a Filter guide having those nasty 26 > nested replaces.. not sure what that would cost the transaction overall, but > I don't see many other ways out of this.. > > Mathew's java scripting plugin idea may be good too, but that again is > creating an external plugin which I do not think they would support. Besides > I don't think we have time enough on hands left for that. > > Joe > > -----Original Message----- > *From:* Action Request System discussion list(ARSList) [mailto: > [EMAIL PROTECTED] Behalf Of *Grooms, Frederick W > *Sent:* Saturday, March 29, 2008 4:16 PM > *To:* [email protected] > *Subject:* Re: Equivalent of the TRANSLATE function in Remedy > > ** 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*(replace(replace(replace(replace(replace(replace(replace(replace(replace(PHONE_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)<http://en.wikipedia.org/wiki/Tr_%28Unix%29>) > 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___ > -- Met vriendelijke groet / Kind regards Michiel Beijen ______________________________________________________ MANSOLUTIONS Energieweg 60-62 3771 NA Barneveld The Netherlands Tel. +31-(0)612968592 Mail [EMAIL PROTECTED] Internet http://bsm.mansolutions.nl _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
