Archives of the TeradataForum

Message Posted: Fri, 24 Oct 2008 @ 15:35:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The attached UDF, translate3, translates any characters to any other. For your application, invoke it like so:

     translate3('ABCD|~ JKL', '######', '1A1B1C1D1E1F'xc)

Admin Comment: I won't be able to add the attachment to the TeradataForum website until tomorrow morning. I'll send a note when they're ready.

In the meantime, I've placed their contents in-line:

     translate3.c
     ------------



     /*--------------------------------------------------------------------
     ** t r a n s l a t e 3
     ** User-Defined Function to translate one character string to
     ** another.  Takes three arguments:
     **   1. input string
     **   2. output translation table
     **   3. input translation table
     ** The characters in (3) that appear in the input string will be
     ** translated to the corresponding characters in (2).
     ** The output table must be at least as long as the input table;
     ** if it isn't, an error is returned.
     **
     ** 2005-04-01  G. Rommel -- initial release
     **
     **------------------------------------------------------------------*/
       /* This pragma is for MP-RAS only. */
     #pragma On(Char_default_unsigned)

     #define SQL_TEXT Latin_Text
     #include 
     #include 

     void translate3 (
        const VARCHAR_LATIN  *in_string,
        const VARCHAR_LATIN  *xlate_table,
        const VARCHAR_LATIN  *in_table,
        VARCHAR_LATIN  *result,
        char  sqlstate[6] )
     {
       int  len, i, pos;
       VARCHAR_LATIN * in_table_ptr;

       if (strlen((char *) xlate_table) < strlen((char *) in_table)) {
          strcpy(sqlstate, "22023");  /* Invalid parameter */
          return;
       }

       len = strlen((char *) in_string);
       for (i=0; i < len; i++) {
          in_table_ptr = strchr(in_table, in_string[i]);
          if (in_table_ptr) {
             pos = in_table_ptr - in_table;
             result[i] = xlate_table[pos];
          } else {
             result[i] = in_string[i];
          }
       }

       result[len] = '\0';
       strcpy(sqlstate, "00000");
       return;
     }





     translate3_cf.txt
     -----------------

     drop function sysdba.translate3;

     create function sysdba.translate3
       ( varchar(4092), varchar(260), varchar(260) )
     returns varchar(4092)
     language C
     no sql
     parameter style td_general
     deterministic
     returns null on null input
     external name
     /* 'CS!translate3!/home/mc71795/src/translate3.c!F!translate3'; */
       'CS!translate3!C:\Documents and Settings\Geoffrey_adm\My
     Documents\UDFs\translate3.c!F!translate3';

     .quit

	<Prev	Next>		<<First	<Prev	Next>	Last>>