Nice thought.. I will need to strip the extension part out first though if I were to go that way.. Will need to toy over that idea and see if I can come out with something better than that..
I still wish Remedy did have a TRANSLATE equivalent! Would have made life so much more easier.. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Brian Goralczyk Sent: Friday, March 28, 2008 7:36 PM To: [email protected] Subject: Re: Equivalent of the TRANSLATE function in Remedy ** Since it seems like no matter what, this is gonna suck, how about this? Create another field. Then have it set the first number out of the field you are trying to clean. You can use the results of a function to populate the value in another function. So test for the location of the first number. Then cut everything before that number and cut that number into the other field. Then keep cycling along until the length of the field is 0. Does that make sense? Brian On Fri, Mar 28, 2008 at 5:52 PM, Thad K Esser <[EMAIL PROTECTED]> wrote: ** 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"
