Oracle using REGEXP to validate a date field

Question

I am using Oracle 11G and I have a date column (Var char 2) where dates are manually entered into the database and unfortunately many times have invalid dates entered. I would like to only select date field that are valid using some sort of REGEXP LIKE statement. Here are the possible valid formats that I would like to select.

DATE

JULY 31, 2009
7/31/2009
31-JUL-09

Anything that's not in these 3 possible formats I do not want to select. Could someone please help me come up with a REGEXP or some other way to select these valid date formats. Thanks in advance.

A very good reason why dates should be saved as **dates** and not as text. — PM 77-1, Apr 01 '13 at 02:16
Are you asking us to write your `REGEXP_LIKE` statement for you? This isn't really a problem, it's something that you could figure out yourself, right? You already know you need to use REGEXP_LIKE, so stop being lazy and write one. — Scotch, Apr 01 '13 at 02:31
Scotch if I knew how to write the REGEXP for all three formats in one REGEXP_LIKE I would do it myself but I can't seem to get it to work. Thanks for the helpful input... — swingard, Apr 01 '13 at 02:36
@Swingard I wasn't trying to be a jerk -- It just seems to me that you already know what to do, you just want someone to do it for you -- Which is kind of against the grain. So it looks like you could either write three regexps and use `or` or use a `not regexp like` to reject particular common dirty data. — Scotch, Apr 01 '13 at 02:53
Also, check this out http://stackoverflow.com/questions/51224/regular-expression-to-match-valid-dates — Scotch, Apr 01 '13 at 02:55
I was just trying to see if I could cut down on the code and do it all in one REGEXP. I think I'll go the route of just using 3 different REGEXP's and OR. Thanks ! — swingard, Apr 01 '13 at 03:23

score 1 · Accepted Answer · answered Apr 01 '13 at 03:30

Try PL/SQL instead of a regular expression. It will be significantly slower, but will be safer and easier to maintain and extend. You should rely on the Oracle format models to do this correctly. I've seen lots of attempts to validate this information using a regular expression, but I rarely see it done correctly.

If you really care about performance, the real answer is to fix your data model.

Code and Test Cases:

--Function to convert a string to a date, or return null if the format is wrong.
create or replace function validate_date(p_string in string) return date is
begin
    return to_date(p_string, 'MONTH DD, YYYY');
exception when others then
    begin
        return to_date(p_string, 'MM/DD/YYYY');
    exception when others then
        begin
            return to_date(p_string, 'DD-MON-RR');
        exception when others then
            return null;
        end;
    end;
end;
/

--Test individual values
select validate_date('JULY 31, 2009') from dual;
2009-07-31
select validate_date('7/31/2009') from dual;
2009-07-31
select validate_date('31-JUL-09') from dual;
2009-07-31
select validate_date('2009-07-31') from dual;
<null>

Simple Performance Test:

--Create table to hold test data
create table test1(a_date varchar2(1000)) nologging;

--Insert 10 million rows
begin
    for i in 1 .. 100 loop
        insert /*+ append */ into test1
        select to_char(sysdate+level, 'MM/DD/YYYY') from dual connect by level <= 100000;

        commit;
    end loop;
end;
/

--"Warm up" the database, run this a few times, see how long a count takes.
--Best case time to count: 2.3 seconds
select count(*) from test1;


--How long does it take to convert all those strings?
--6 minutes... ouch
select count(*)
from test1
where validate_date(a_date) is not null;

Oracle using REGEXP to validate a date field

1 Answers1

Linked