Re: checking format in sql

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 22 Jul 2004 13:43:33 -0700
Message-ID: <9711ade0.0407221243.74a8eced@posting.google.com>

lord.of.board_at_gmx.de (Peter Mueller) wrote in message news:<e3b18ff5.0407220659.53f26eb8_at_posting.google.com>...
> 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

You do realise mydate is actually a TIME field, not a date field, and, as such, you need to verify the TIME is in 24-hour format. I imagine you are attempting to prevent anyone from entering time in a 12-hour, AM/PM format. If that is the case, you can implement a check constraint using SUBSTR():

create table xxxx(.....,

                  mydate   varchar2(10) check(substr(mydate, 1, 2)

between '00' and '23' and substr(mydate, 4,2) between '00' and '59' and length(mydate) = 5),

                  .....);

All valid 24-hour format times will be 5 characters in length, and no more (which leads me to question the choice of a varchar2(10) for this data). Entering any time with an AM/PM tag will automatically invalidate the check constraint and, will thus be rejected. Of course, this will not populate an error table unless you are using PL/SQL and handling the exception generated:

declare

     wrong_format exception;
     pragma exception_init(wrong_format, -2290);
     .....
begin
     .....
     insert into xxxx values(....,....,:my_date,....);
     .....
exception
     when wrong_format then
            insert into problem_records (reason, tablename, data)
            values('Incorrect date format','XXXX',:my_date);
     .....

end;

A trigger won't populate your problem_records table since, on error, the transaction is rolled back, thus, nothing to insert. Using a procedure to populate this data is probably the best way to do such a task, using the check constraint I provided. Besides, you HAVE the errant value submitted by the user, so it is unnecessary to select values from your table. It is also foolish to allow bad data into your table to begin with, especially if you know what is bad and can do something to prevent such data from being inserted. True, my solution is somewhat of a workaround, since you're indirectly validating the format mask, but it does work, and will not allow values such as '01:43PM' (the string is too long) or '22:60' (60 is an invalid number of minutes).

I hope this helps.

David Fitzjarrell Received on Thu Jul 22 2004 - 15:43:33 CDT