checking format in sql

From: Peter Mueller <lord.of.board_at_gmx.de>
Date: 22 Jul 2004 07:59:31 -0700
Message-ID: <e3b18ff5.0407220659.53f26eb8@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 Received on Thu Jul 22 2004 - 09:59:31 CDT