利用正则表达式找出不合符的日期

   很多时候我们的日期可能存放的是字符串类型，在插入的时候也很有可能插入的日期格式不正确，
因此我们需要找出这些不合符的日期格式，来此来修正。当然可以使用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.