Oracle使用REGEXP验证日期字段 [英] Oracle using REGEXP to validate a date field

问题描述

我正在使用Oracle 11G，并且有一个日期列(Var char 2)，其中日期是手动输入到数据库中的，不幸的是很多次输入了无效的日期.我只想使用某种REGEXP LIKE语句选择有效的日期字段.这是我想选择的可能有效格式.

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

我不想选择这3种可能格式之外的任何内容.有人可以帮我提出一个REGEXP或其他选择这些有效日期格式的方法.预先感谢.

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.

推荐答案

尝试使用PL/SQL而不是正则表达式.这将大大降低速度，但更安全，更易于维护和扩展. 您应该依靠Oracle格式模型来正确执行此操作.我已经看到了很多尝试使用正则表达式来验证此信息的尝试，但是 我很少看到它能正确完成.

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.

代码和测试用例:

--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>

简单性能测试:

--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使用REGEXP验证日期字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！