Archives of the TeradataForum

Message Posted: Thu, 15 Sep 2005 @ 09:48:42 GMT

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

In DB2, TRANSLATE(A,B,C) says for every occurrence in A of a character in C, substitute the corresponding character from B. If B is shorter than C it is considered padded on the right with spaces.

     TRANSLATE(RTRIM(LTRIM( SUBSTR(COL_1,1,6) )),'$',' 0123456789') = ''

Strips leading (LTRIM) and trailing (RTRIM) spaces from the substring, then translates embedded spaces to $ and digits to spaces and leaves any other character unchanged, then checks to be sure there is nothing but spaces left.

In short, it's checking to be sure the substring contains only numeric digits, optionally with leading and/or trailing spaces.

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