0

What is the regular expression to be used to catch anything in the database which is not of the expression MM/DD/YYYY. (I want everything which is apart from the above mentioned format, could be dd-mon-yy or yyyy/mm/dd etc)

I am using the below regexp query

select birth_date FROM table_name where not regexp_like (birth_date, '[0-9][0-9]/[0-9][0-9]/[0-9]{4}');
Shubham Sharan
  • 23
  • 2
  • 7

2 Answers2

2

Firstly, a suggestion to you , don't use a VARCHAR2 / CHAR type for DATEs in database.

You may create a function using TO_DATE

CREATE OR REPLACE FUNCTION validmmddyyyy (p_str IN VARCHAR2)
   RETURN NUMBER
AS
   V_date   DATE;
BEGIN
   V_Date := TO_DATE (p_str, 'MM/DD/YYYY');
   RETURN 1;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 0;
END;

select validmmddyyyy('09/12/2018') from DUAL;
1
select validmmddyyyy('13/12/2018') from DUAL;
0
select validmmddyyyy('2018/12/01') from DUAL;
0

Use your query like,

select birth_date FROM table_name where validmmddyyyy(birth_date) = 0

If you are lucky enough to use Oracle 12c R2, you could make use of DEFAULT..ON..CONVERSION ERROR clause of TO_DATE

SELECT *
  FROM table_name
 WHERE TO_DATE (birth_date default null on conversion error,'MM/DD/YYYY') IS NULL
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
-1

You can use the below query to get other than the required format :

select * from dual where not regexp_like('2013/24/feb','[0-9]{2}.[[:alpha:]]{3}.[0-9]{4}')

balaiah
  • 24
  • 2