Archives of the TeradataForum

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

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

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>>