Oracle varchar2到日期转换并将不一致值替换为null

SQL> create or replace
  2  function date_convert(p_str varchar2) return date is
  3    x date;
  4  begin
  5    x := to_date(p_str,'dd/mm/yyyy');
  6    return x;
  7  exception
  8    when others then return null;
  9  end;
 10  /

Function created.

SQL>
SQL>
SQL> drop table t1 purge;

Table dropped.

SQL>
SQL> create table t1 ( str varchar2(20));

Table created.

SQL>
SQL> insert into t1 values ('1');

1 row created.

SQL> insert into t1 values ('2');

1 row created.

SQL> insert into t1 values ('10/10/2014');

1 row created.

SQL> insert into t1 values ('10/12/2014');

1 row created.

SQL> insert into t1 values ('10/99/2014');

1 row created.

SQL>
SQL> select date_convert(str) from t1;

DATE_CONVERT(STR)
-------------------


10/10/2014 00:00:00
10/12/2014 00:00:00

SQL> select
  2    case
  3      when length(str) between 8 and 12
  4         and str like '%/%/%' then date_convert(str)
  5    end  dte
  6  from t1;

DTE
-------------------


10/10/2014 00:00:00
10/12/2014 00:00:00