Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


Subj:   Re: Eternally Foolproof Casting from text to number
 
From:   SPIEGEL Andrea

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020