Archives of the TeradataForum

Message Posted: Tue, 23 May 2006 @ 09:18:59 GMT

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

If you are on V2R5.1 or higher you can use a UDF. Download the oracle UDF library from the Teradata download web page and use the oTranslate UDF. Here are some examples:

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     select * from t2;

      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

     a
     --------------------
     G?nter

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     select otranslate(a, '?', 'u') from t2;

     *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

     otranslate(a,'?','u')
     -------------------------------------------------------------------
     Gunter

The following drops the characters instead:

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     select otranslate(a, ' ?', ' ') from t2;

      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

     otranslate(a,' ?',' ')
     -------------------------------------------------------------------
     Gnter

The from and to conversion string can have a whole set of characters to translate. To eliminate ASCII control characters for example do the following:

     select
     otranslate(x,
     '200102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'xcv, '
     ') from t3;

The only control character it can't eliminate is a 0x00 since that is a C string termination character. To do that you would have to modify otranslate to pass a CHAR instead of VARCHAR string and modify the code within it.

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