Data validation In oracle

Question

Hi Have table in oracle like

ID          NAME      STARTDATE
1           A         2014-01-01
2           B         1900-01-01
3           C         29-02-2016

Here while executing select query I want to put data validation which check year between 1900-2099 month 1-12 and date between 1-31. I try to get this result by using regular expression in my query

SELECT *
FROM   test
WHERE  REGEXP_LIKE (
          startdate,
          '^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$')

it's working fine but with the case 31-02-2016 its failing, I tried to use another expression which given in this link Regex to validate date format dd/mm/yyyy

but it's showing no data found while executing the query, Is there any limit of oracle with regular expression as the same expression working fine on this http://regexr.com/

Just create a DATE column, store the data in there ... and no fancy checks needed. Don't re-invent the wheel. — Ditto, Feb 13 '15 at 20:01
You've tagged this for Oracle and MySQL. Which database are you using? Why are you storing a date in a `varchar2` column when there is a proper `date` data type available? — Justin Cave, Feb 13 '15 at 20:02
Hey @Ditto table is already in place and have lac of data and from lac data need to get only those data which have valid data as I mention above — Amit, Feb 13 '15 at 20:04
@Amit: so convert it to a date via something like: `case when substr(col,3,1)='-' then to_date(col,'dd-mm-yyyy') else to_date(col,'yyyy-mm-dd') end.` You could likely use regexp in there, however, in this case, simpler is better. ID the most simples rules for each format you can, and then convert. Add as many as you need. Bottom line: Convert it to a date ... one way or the other. — Ditto, Feb 13 '15 at 20:39

score 1 · Accepted Answer · answered Feb 13 '15 at 21:18

I don't know a function that do this, but you can create a function that validates an specific format using to_date. For example:

create or replace function test_date_func(d varchar2,format varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,format) into v_date from dual;
  return '1';
  exception when others then return '0';
end;

Now you can call the function many times as formats you which:

select dat, test_date_func(dat,'dd-mm-yyyy')||test_date_func(dat,'yyyy-mm-dd') valid 
from (select '2014-01-01' dat from dual union all
select '1900-01-01' from dual union all
select '29-02-2010' from dual union all
select '28-02-2010' from dual);

This validates dates in formats 'dd-mm-yyyy' and 'yyyy-mm-dd' and return:

DAT     VALID
2014-01-01   01
1900-01-01   01
29-02-2010   00
28-02-2010   10

As you can see the unique invalid date is 29-02-2010 which return 0 in both cases 00. Now if you want this more clear, you can add a case condition to show Valid or Invalid instead 0s and 1s:

select dat, case when regexp_like(valid,'1') then 'Valid' else 'Invalid' end from ( 
select dat, test_date_func(dat,'dd-mm-yyyy')||test_date_func(dat,'yyyy-mm-dd') valid 
from (select '2014-01-01' dat from dual union all
select '1900-01-01' from dual union all
select '29-02-2010' from dual union all
select '28-02-2010' from dual));

Data validation In oracle

1 Answers1