 |
 |
Archives of the TeradataForum
Message Posted: Fri, 15 Dec 2006 @ 19:16:15 GMT
| 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
| |
