Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Stripping numeric data in varchar(900)
 
From:   Prescott, Kyle R

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023