Thread

1. Oracle Compatibility (Translate function)

   Edwin Ramirez <ramirez@doc.mssm.edu> — 1999-12-16T14:24:14Z

   Hello,
   
       I have modified the translate function in order to improve its
   compatibility with Oracle.  It now supports the replacement of multiple
   characters and it will also shorten the length of the string when characters
   are replaced with nothing.
   
   [Note: The arguments are different from the original translate]
   Can this function replace the existing function in the distribution?
   
   -------NEW FUNCTION--------------------------------------
   text *
   translate(text *string, text *from, text *to)
   {
           text       *ret;
           char       *ptr_ret, *from_ptr, *to_ptr, *source, *target, *temp,
   rep;
           int        m, fromlen, tolen, retlen, i;
   
           if ((string == (text *) NULL) ||
                   ((m = VARSIZE(string) - VARHDRSZ) <= 0))
                   return string;
   
           target   = (char *) palloc(VARSIZE(string) - VARHDRSZ);
           source   = VARDATA(string);
           temp     = target;
   
           fromlen = VARSIZE(from) - VARHDRSZ;
           from_ptr = VARDATA(from);
           tolen = VARSIZE(to) - VARHDRSZ;
           to_ptr   = VARDATA(to);
           retlen = 0;
           while (m--)
           {
             rep = *source;
             for(i=0;i<fromlen;i++) {
               if(from_ptr[i] == *source)  {
                 if(i < tolen) {
                   rep = to_ptr[i];
                 } else {
                   rep = 0;
                 }
                 break;
               }
             }
             if(rep != 0) {
               *target++ = rep;
               retlen++;
             }
             source++;
           }
   
           ret = (text *) palloc(retlen + VARHDRSZ);
           VARSIZE(ret) = retlen + VARHDRSZ;
           ptr_ret = VARDATA(ret);
           for(i=0;i<retlen;i++) {
             *ptr_ret++ = temp[i];
           }
           pfree(target);
           return ret;
   }
   
   
   Thanks,
   Edwin S. Ramirez
   
   
   
   

2. Re: [HACKERS] Oracle Compatibility (Translate function)

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-12-17T17:21:57Z

   > I have modified the translate function in order to improve its
   > compatibility with Oracle.  It now supports the replacement of 
   > multiple characters and it will also shorten the length of the string 
   > when characters are replaced with nothing.
   > [Note: The arguments are different from the original translate]
   > Can this function replace the existing function in the distribution?
   
   afaik yes. Does anyone have a problem with this (it allows
   substitution of multiple characters)? I think the system tables will
   need to be updated; I'll do this within the next week or so if noone
   else has already taken this on.
   
   btw, there is some chance that when we go to native support for
   NATIONAL CHARACTER etc then TRANSLATE() will need to become SQL92
   compliant (and basically a different function). But that is an issue
   for later, and we may be able to solve it without having to give up on
   the Oracle version.
   
                     - Thomas
   
   -- 
   Thomas Lockhart				lockhart@alumni.caltech.edu
   South Pasadena, California