Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 15 Dec 2006 @ 19:16:15 GMT


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


Subj:   Re: Looking for bad data
 
From:   Victor Sokovin

  I'm trying to find bad data in a character field. The correct format should be numbers only in the first four positions, followed by an 'M' in the 5th position. In SQL Server, I would write something like,  


  WHERE field1 LIKE( '[0-9][0-9][0-9][0-9][0-9,M]')  


  Is there anything equivalent in Teradata?  


Until we have support for regular expressions we use all kinds of tricks. Many of them are mentioned in the archives and some are really ingenious but I'd like to refer you to one of my own postings (of course):

www.teradataforum.com/teradata/20040617_114448.htm.


I used to fight on the Forum against comparison of LOWER and UPPER case values of the same string as not only numbers but some non-ASCII chars can easily bypass such a filter. I am glad nobody mentions that method anymore.

But I am a little puzzled why nobody mentions the easy comparison with the lowest value char, which is 'A'. If digits are considered as chars their value is less than that of 'A'. The method might need some refinement if you need to filter out special characters but it is also easy to implement. I think this comparison is quite fast, too.


Regards,

Victor



     
  <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