很多时候我们的日期可能存放的是字符串类型，在插入的时候也很有可能插入的日期格式不正确，
因此我们需要找出这些不合符的日期格式，来此来修正。当然可以使用TO_DATE函数一个一个的转换来找出不合法的日期。ORACLE提供了正则表达式，正则表达式在处理不合符的IP ,手机号,EMAIL等大有用处。
此处我们用正则表达式找出不合法的日期格式。
SQL> conn ysp/ysp
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table regexp_test(datecol char(19));
Table created.
SQL> insert into regexp_test select LAST_DDL_TIME from user_objects where rownum<=10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select * from regexp_test;
DATECOL
--------------------------------------
2010-07-06 16:56:07
2010-06-12 16:16:36
2010-06-12 13:44:14
2010-06-12 14:23:33
2010-06-12 14:48:21
2010-07-26 10:44:27
2010-06-04 10:20:03
2010-06-04 13:22:31
2010-06-04 10:26:31
2010-06-04 13:50:02
10 rows selected.
此处我们用YYYY-MM-DD HH24:MI:SS作为我们的日期格式，其他格式为非法。
SQL> update regexp_test set datecol='2010-00-06 16:56:07' where datecol='2010-07-06 16:56:07';
1 row updated.
SQL> update regexp_test set datecol='2010-06-00 16:16:36' where datecol='2010-06-12 16:16:36';
1 row updated.
SQL> update regexp_test set datecol='2010-06-12 24:44:14' where datecol='2010-06-12 13:44:14';
1 row updated.
SQL> update regexp_test set datecol='2010-06-12 14:60:33' where datecol='2010-06-12 14:23:33';
1 row updated.
SQL> update regexp_test set datecol='2010-06-12 14:48:67' where datecol='2010-06-12 14:48:21';
1 row updated.
SQL> commit;
Commit complete.
SQL> insert into regexp_test values('2010-06-04:13:50:02');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from regexp_test;
DATECOL
---------------------------
2010-00-06 16:56:07 --月份不合法
2010-06-00 16:16:36 --日子不合法
2010-06-12 24:44:14 --小时不合法
2010-06-12 14:60:33 --分钟不合法
2010-06-12 14:48:67 --秒数不合法
2010-07-26 10:44:27
2010-06-04 10:20:03
2010-06-04 13:22:31
2010-06-04 10:26:31
2010-06-04 13:50:02
2010-06-04:13:50:02 --格式不合法
11 rows selected.
找出合法的日期
SQL> select * from regexp_test
2 where regexp_like(datecol,'^([0-9]{4})-((0[1-9])|(1[0-2]))-((0[1-9])|([1-2][0-9])|(3[0-1])) (([0-1][0-9])|(2[0-3])):([0-5][0-9]):([0-5][0-9])$');
DATECOL
--------------------------------------
2010-07-26 10:44:27
2010-06-04 10:20:03
2010-06-04 13:22:31
2010-06-04 10:26:31
2010-06-04 13:50:02
找出不合法的日期
SQL> select * from regexp_test
2 where not regexp_like(datecol,'^([0-9]{4})-((0[1-9])|(1[0-2]))-((0[1-9])|([1-2][0-9])|(3[0-1])) (([0-1][0-9])|(2[0-3])):([0-5][0-9]):([0-5][0-9])$');
DATECOL
--------------------------------------
2010-00-06 16:56:07
2010-06-00 16:16:36
2010-06-12 24:44:14
2010-06-12 14:60:33
2010-06-12 14:48:67
2010-06-04:13:50:02
6 rows selected.
转播
