Archives of the TeradataForum

Message Posted: Fri, 15 Dec 2006 @ 19:23:10 GMT

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

depending on how non-numeric your data is, you might consider adding this comparison to the previous suggestion by Christian:

     WHERE  substr(field1,5,1)(cs) = 'M'  AND /* check M first */
     upper((field1(char(4)))) = lower((field1(char(4)))) AND
     /*  eliminates obvious non-numeric before checking individual digits */
     >        substr( field1, 1, 1 ) between 0 and 9 and
     >        substr( field1, 2, 1 ) between 0 and 9 and
     >        substr( field1, 3, 1 ) between 0 and 9 and
     >        substr( field1, 4, 1 ) between 0 and 9

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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