Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 23 Jul 2007 @ 14:18:22 GMT


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


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

Hi,

Would this be a valid solution?:

     create table tb_num(
     col char(02)
     );
     insert into tb_num values('a9');
     insert into tb_num values(' 8');
     insert into tb_num values('8#');
     insert into tb_num values('#8');
     insert into tb_num values('%%');

     Select cola,
            colb
     From(
        select trim(both from(col)) as cola,
           substr(cola,1,1) as col1,
           substr(cola,2,1) as col2,
           case when index('0123456789',col1) > 0
                         then 'NUM' else '' end as c11,
           case when index('0123456789',col2) > 0
                         then 'NUM' else '' end as c22,
           case when c11 = c22 and c11 = 'NUM'
                         then 'isNumeric' else '' end as colb
        from tb_num
        )xx
     Order by 1,2;

Or similar.

Andrea



     
  <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: 15 Jun 2023