Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    18 Sur 3917

    Question Unanswered: Wanta to know is not numbrer or valdidate date


    I have one table with two fields:

    CODIGO_POSTAL varchar2(100),
    FE_NAC varchar2(100),

    the first one must be a number, and the second one must be a date.

    but for example:

    CODIGO_POSTAL, contains

    I want to know wich of this have a letter or another caracters no numbers???

    the same in dates, i need this format, (dd/mm/YYYY)

    Best Regards...

  2. #2
    Join Date
    Jul 2006
    One of the many possible solution is outlined below. It has two steps:

    1. Create a function for each type of test you need to perform: one function to test values as numbers, one function to test values as dates, etc.

    2. Create a trigger on your table, so whenever data is inserted or updated the appropriate functions are called to test the data. If a function returns a negative answer to the trigger, then the trigger should set the corresponding piece of data to NULL in the table.

    For example, when your trigger calls a function like the one shown below it will get back a 1 if the input value was a number, or a 0 if the input parameter was not a number.

    create or replace function fxn_is_number_valid
    (i_number in varchar2)
    return number
    v_number number;
    v_answer number;
    v_answer := 0;
    v_number := to_number(i_number);
    v_valid := 1;
    when others then v_valid := 0;
    return v_valid;

  3. #3
    Join Date
    Dec 2003
    To check for numbers only

    select CODIGO_POSTAL 
    from TABLE
    where regexp_instr(CODIGO_POSTAL ,'[[:alpha:]]') = 0
    To check for the date, why don't you just set the field's datatype up as a date? You'll have to specify TO_DATE in your loads, but you shouldn't let the data get as far as into your table (unless this is a staging table of some kind or something).

    If you have to, you could probably set up a FUNCTION for this one, as suggested above.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts