Archives of the TeradataForum
Message Posted: Thu, 15 Sep 2005 @ 09:48:42 GMT
| Subj: || || Re: Teradata equivalent of DB2 TRANSLATE |
| From: || || Fred W Pluebell |
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.