Re: checking format in sql

From: Thomas Gaines <Thomas.Gaines_at_noaa.gov>
Date: Thu, 22 Jul 2004 16:03:23 -0600
Message-ID: <410039AB.8000504@noaa.gov>

Peter -

After reading David's response, I realized that I mistakenly considered the hour:minute mask as the input instead of the actual time. With that in mind, I'll amend my example as follows.

drop table xxxx;

create table xxxx (mydate      varchar2(10),
                  data        varchar2(30));

insert into xxxx values ('13:00','row 1');
insert into xxxx values ('7:03','row 2');

insert into xxxx values ('27:00','row 3'); insert into xxxx values ('13:00:05','row 4');

• Create the test function

create or replace function check_date_format (time_field varchar2) return number is
  dummy_date date;
begin

• This function will do a dummy date assignment using the
• passed-in date format. If an error occurs, then the date
• format is no good and the "bad" value is returned.
• Else, the "good" value is returned. dummy_date := to_date('07/21/2004 ' || time_field,'mm/dd/yyyy hh24:mi'); return 1;

exception
  when others then
    return 0;   

end;
/

• And then do some test queries using it.

select 'wrong date format', 'xxxx', data from xxxx
where check_date_format(mydate) <> 1;

I thought that I would be able to use this user-defined function in a check constraint on this particular column. But when I try it, I error with an ORA-00904: "CHECK_DATE_FORMAT": invalid identifier. I haven't checked the docs, but I think that one is not allowed to do this. No big deal, though. Just create a trigger and do the check there...

Peter Mueller wrote:

>Hi,
>
>I would like too know, if somebody has an idea on how to select
>all datasets that do not match the following criteria:
>
>column_name datatype format example
>mydate varchar(10) HH24:MI 13:00
>
>What I would like to do is:
>
>insert into problem_records (reason, tablename, data)
>select 'wrong date format', 'xxxx', 'yyyy'
>from xxxx
>where mydate <> ??????does not match format?????
>
>I thought I could use a check in the database schema like:
>check( to_date( mydate, 'HH24:MI' ) ), but oracle kept complaining
>about using that function in a check statement. That's why I would
>like to write something that checks this format. It would be nice,
>if I do not have to use PL/SQL.
>
>I hope somebody can help me.
>
>Best regards,
>lordi
>
>
Received on Thu Jul 22 2004 - 17:03:23 CDT