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)<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___ > > ***IMPORTANT NOTICE: This communication, including any attachment, > contains information that may be confidential or privileged, and is intended > solely for the entity or individual to whom it is addressed. If you are not > the intended recipient, you should delete this message and are hereby > notified that any disclosure, copying, or distribution of this message is > strictly prohibited. Nothing in this email, including any attachment, is > intended to be a legally binding signature.*** > __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"
