Archives of the TeradataForum

Message Posted: Tue, 28 Aug 2007 @ 16:22:55 GMT

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

There is a nice UDF already written by PS for Teradata. It can be found in the Oracle sourced UDF's and downloaded freely from Teradata. It's called Translate.

The translate function searches for each character in the string and replaces with the value of the third parameter (1:1 match between each character in the 2nd and 3rd parameter string arrays).

     TRANSLATE(,
          ,);

Example:

     Translate('ABC 1237', 'ABC123','XyZ456') = 'XyZ 4567'

To remove numerics, place the numbers in the search characters and place the corresponding translated characters in the second string (in this case nothing so and empty string will remove them).

     Translate (SOURCE_DATA_STRING,'0123456789','');

Kyle Prescott | Database Administrator | Unum-IODS | Chattanooga, TN

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