CODEWITHSUNDEEP
sqlregexoracle-database
Lorik Berisha
Lorik Berisha

Reputation: 273

Checking a specific date format in Oracle SQL using RegexLike

I am using oracle data integrator as an ETL tool to load data, there is one column which as a source comes in format YYYYMMDD for ex: 20190418. I want to query that table to identify all those records which do not fit 20190418 or YYYYMMDD format. P.S. the column is in varchar2 datatype.

I already tried using something like this: 

SELECT  CASE WHEN NOT REGEXP_LIKE('20190418', '^\d{4}(0[1-9]|1[12])(0[1-9]|[12]\d|3[01])$') then '00000000' else '20190418' END FROM DUAL;

This seems to identify those illegal dates, but for example it didn't work for this one : '20181023'.

Can someone figure it out, I think I am missing some kind of pattern

Upvotes: 0

Views: 504

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

Using PL/SQL maybe a better alternative

declare
   v_date     date;
begin
  for c in
  (
   select '20190418' as date_str from dual union all
   select '20191804' from dual union all
   select '201904187' from dual
  )
  loop
    begin    
       v_date := to_date(c.date_str,'yyyymmdd');    
       dbms_output.put_line(c.date_str);
      exception when others then null;   
    end;    
  end loop;  
end;

gives only the decently formatted data as output. In the above case, it's 20190418 only.

For 20191804, we would get ORA-01843: not a valid month

For 201904187, we would get ORA-01830: date format picture ends before converting entire input string

errors

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

I would propose a function rather than a regex, will be easier.

CREATE OR REPLACE FUNCTION Verify_date_number(inNumber IN NUMBER) RETURN VARCHAR2 AS        
    res DATE;
BEGIN   
    res := TO_DATE(inNumber, 'fxYYYYMMDD');       
    RETURN TO_CHAR(res, 'YYYYMMDD');       
EXCEPTION    
    WHEN OTHERS THEN        
        RETURN '00000000';
END;

If you are running Oracle 12.2 then you can also use VALIDATE_CONVERSION 

SELECT
   CASE VALIDATE_CONVERSION('20190418' AS DATE, 'fxYYYYMMDD')
   WHEN 1 THEN '20190418'
   ELSE '00000000'
   END
FROM dual;

Upvotes: 2

hotfix
hotfix

Reputation: 3396

you can try this one:

with tab as(
  select '20190418' as dat from dual union all
  select '20181023' as dat from dual union all  
  select '20181123' as dat from dual union all  
  select '20181223' as dat from dual union all  
  select '20181201' as dat from dual union all  
  select '20181209' as dat from dual union all  
  select '20181210' as dat from dual union all  
  select '20181229' as dat from dual union all  
  select '20181231' as dat from dual union all  
  select '20181232' as dat from dual union all    
  select '20181200' as dat from dual union all  
  select '20191418' as dat from dual 
)
SELECT  CASE WHEN NOT REGEXP_LIKE(dat, '^\d{4}(0[1-9]|(1[0-2]))(0[1-9]|[1-2][0-9]|3[0-1])$') then '00000000' else dat END  as dat

FROM tab;

Result:

20190418
20181023
20181123
20181223
20181201
20181209
20181210
20181229
20181231
00000000
00000000
00000000

Upvotes: 0

Related Questions