CODEWITHSUNDEEP
regexoracle-databasedate
Amit
Amit

Reputation: 495

Data validation In oracle

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/

Upvotes: 1

Views: 534

Answers (1)

Aramillo
Aramillo

Reputation: 3216

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

Upvotes: 1

Related Questions