Archives of the TeradataForum

Message Posted: Mon, 23 Jul 2007 @ 15:15:25 GMT

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

Sorry, it was not exactly right. Here' s what I meant:

     create table tb_num(
     col char(03)
     );
     insert into tb_num values('a91');
     insert into tb_num values(' 8 ');
     insert into tb_num values('8#q');
     insert into tb_num values('#8c');
     insert into tb_num values('%a%');
     insert into tb_Num values('910');


     sel col,
        index('0123456789',substr(trim(both from(col)),1,1)) as IND1,
        index('0123456789',substr(trim(both from(col)),2,1))  as IND2,
        index('0123456789',substr(trim(both from(col)),3,1)) as IND3,
        case when zeroifnull(IND1) > 0 and zeroifnull(IND2) > 0 and
             zeroifnull(IND3) > 0 then 'isNumeric' else '' end as C3
     from tb_num
     order by 1;

     col   IND1   IND2   IND3   C3
       8      9      1      1   isNumeric
     #8c      0      9      0
     %a%      0      0      0
     8#q      9      0      0
     910     10      2      1   isNumeric
     a91      0     10      2

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