Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sun, 31 Mar 2002 @ 20:49:40 GMT


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


Subj:   Re: Numeric Value Validation
 
From:   fred & uli

  does anybody know a function in V2R4.0.3 to validate if a returnstring contains a numeric value? I want to cast a substring of a char field in the case it is numeric....  



Hi Carmen,

I played a bit around and found the solution below.

I do really not recommend to use this in production and if you like to check for real decimal or negative values you need some extensions - but it works. I remember that we added an enhancement request for a check function that is able to check if characters are valid dates. Maybe this function can developed to be more general.

Does anybody know if NCR is really working on the implementation?


Have fun!

Ulrich


Example DDL, test data and SQL:


     CREATE TABLE TEST.test
          (
           textfield VARCHAR(200)
          )
     UNIQUE PRIMARY INDEX ( textfield );

     insert into test values ('92723762');
     insert into test values ('3846728473265');
     insert into test values (' sa7277632');
     insert into test values ('123443y7');
     insert into test values ('1234437');
     insert into test values (' 1  234437');
     insert into test values ('sdjjsdl  sd ');
     insert into test values ('9402w8267');
     insert into test values ('82828j2');
     insert into test values ('8736 23515267');
     insert into test values ('94302828382k');
     insert into test values ('00439327723472');
     insert into test values (' 7290329357');
     insert into test values ('1');
     insert into test values ('234');
     insert into test values ('74672');
     insert into test values ('272j22347');

     select  master.textfield,
             cast(master.textfield as decimal(18,0)) as number,
             type(master.textfield),
             type(number)
     from    test master,
             (
                     select  t.textfield,
                             characters(t.textfield) as field_length,
                             count(*) as number_of_num
                     from    test t,
                             (
                             select  str !! num as check_string,
                                     id
                             from    (
                                     select  trim(both from
     calendar_date-current_date) !! '%'  as num
                                     from    Sys_Calendar.Calendar
                                     where   num between 0 and 9
                                     ) as tmp,
                                     (
                                     select  calendar_date-current_date as id,
                                             /* "_" varchar times */

     substring('_________________________________________________________________
     ____________________________________________________________________________
     ___________________________________________________________'
     from 1 for id) as str
                                     from    Sys_Calendar.Calendar
                                     /* set to varchar field */
                                     where id between 0 and 200
                                     ) as s
                             ) c
                     where   t.textfield like c.check_string
                             and c.id <= characters(t.textfield)
                     group by 1,2
                     having field_length = number_of_num
             ) as check_tab
     where master.textfield = check_tab.textfield
     ;


     
  <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: 27 Dec 2016