Re: checking format in sql

From: Thomas Gaines <Thomas.Gaines_at_noaa.gov>
Date: Thu, 22 Jul 2004 14:22:28 -0600
Message-ID: <41002204.3020703@noaa.gov>

Peter -

Why the aversion to PL/SQL? Assuming that you will allow hour:minute formats that are legal in the Oracle sense and not just the string "HH24:MI", then something like this would work well for you.

It uses PL/SQL, but I can't think of a way NOT to use a little chunk of PL/SQL in this case...

drop table xxxx;

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

insert into xxxx values ('hh24:mi','row 1');
insert into xxxx values ('hh24:MI','row 2');

insert into xxxx values ('garbage:mi','row 3'); insert into xxxx values ('hh24:mi:ss','row 4');

• Create the test function

create or replace function check_date_format (date_format varchar2) return number is
  dummy_date date;
  date_format_mask varchar2(100) := 'mm/dd/yyyy ' || date_format; 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/22/2004 13:00',date_format_mask); 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;

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 - 15:22:28 CDT