oracle regex_replace to keep digit and backslash

Question

I have excel file that contains some junk data in date field.

the date format is in 1/2/2015 format. I'm trying to load that file into a stage table in varchar field and apply regex replace function to clean up the bad data before loading to main table

can i somebody provide me the suitable experssion for this

create table A
(
bad_date varchar2(4000)
);

insert into A
( bad_date)
values ('1/2/2005');
insert into A
( bad_date)
values ('indep');
insert into A
( bad_date)
values ('/');

commit;


 create table B
    (
    good_date date
    );

I want to use regex function to cleanse the data which is not in the date pattern. Thanks for your help!

Barbaros Özhan · Accepted Answer · 2018-05-31 02:40:48Z

3

Use ^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4} pattern for regexp_like conforming your date format.

Use the following insert statement to get clean date data :

insert into B
select * from
( select to_date(bad_date,
           case when 
             regexp_like(bad_date,'^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}') then 
             'DD/MM/YYYY'
           end) dateConverted          
    from A)
where dateConverted is not null;

SQL Fiddle Demo

edited May 31, 2018 at 2:40

answered May 31, 2018 at 2:29

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-05-30 23:16:46Z

3

You can come close with something like:

select (case when regexp(bad_date, '^[0-1]?[0-9]/[0-3]?[0-9]/[0-9]{4}$')
             then to_date(bad_date, 'MM/DD/YYYY'
        end) as converted_date

answered May 30, 2018 at 23:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2018-05-30 23:21:14Z

3

Use the following:

INSERT INTO B (GOOD_DATE)
  SELECT TO_DATE(BAD_DATE, 'DD/MM/YYYY')
    FROM A
    WHERE REGEXP_LIKE(BAD_DATE, '[0-9]+/[0-9]+/[0-9]+')

SQLFiddle here

Best of luck.

answered May 30, 2018 at 23:21

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Comments

wp78de · Accepted Answer · 2018-05-31 17:30:40Z

2

I am inclined to contribute a more mature regex to match valid dates in m/d/yyyy format:

INSERT INTO B (GOOD_DATE)
  SELECT TO_DATE(BAD_DATE, 'DD/MM/YYYY')
    FROM A
    WHERE REGEXP_LIKE(BAD_DATE,
            '^(0?[1-9]|[12][0-9]|3[01])\/(0?[1-9]|1[012])\/(19|20)[0-9][0-9]$'
    )

SQLFiddle

Inspired by

edited May 31, 2018 at 17:30

answered May 31, 2018 at 2:07

wp78de

19.1k7 gold badges49 silver badges78 bronze badges

Collectives™ on Stack Overflow

oracle regex_replace to keep digit and backslash

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related