REGEXP_LIKE适用于不喜欢MM/DD/YYYY的任何内容 [英] REGEXP_LIKE for anything not like MM/DD/YYYY

问题描述

什么是用于捕获数据库中不是 MM/DD/YYYY 的不是的任何内容的正则表达式. (我希望除上述格式之外的所有内容都可以是dd-mon-yy或yyyy/mm/dd等)

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

推荐答案

首先，对您的建议是，不要对数据库中的DATE使用VARCHAR2 / CHAR类型.

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

您可以使用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

使用类似的查询

select birth_date FROM table_name where validmmddyyyy(birth_date) = 0

如果有幸使用Oracle 12c R2，可以使用TO_DATE

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

这篇关于REGEXP_LIKE适用于不喜欢MM/DD/YYYY的任何内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！