find a date range with regular expression Oracle SQL

create table parsetest (toparse varchar2 (100);

insert into parsetest values ('jan 3 2009 - dec 31 2009');
insert into parsetest values ('jan 3 2009-dec 31 2009');
insert into parsetest values ('feb 3 2009-dec 31 2009');
insert into parsetest values ('mar 3 2009-dec 31 2009');
insert into parsetest values ('apr 3 2009-dec 31 2009');
insert into parsetest values ('may 3 2009-dec 31 2009');
insert into parsetest values ('jun 3 2009-dec 31 2009');
insert into parsetest values ('jul 3 2009-');
insert into parsetest values ('3 jul 2009 - aug 31 2009, 1 oct 2009-');
insert into parsetest values ('3 aug 2009 - aug 31 2009, 1 oct 2009-31 dec 2009');
insert into parsetest values ('jan 2009');
insert into parsetest values ('feb 2009');

commit;

Open in new window


On an Oracle 11 database, I'm wanting a regular expression that returns all records that contain a range of data, these can be identified by the field having the following content:

a date : mmm d(d) yyyy
and a hyphen : the hyphen may come directly after the year but there may be one or more spaces preceding it.  The hyphen may also come before the date that matches the above pattern, for example see record 9 in the table.

Given the data in the table created above, I want the following data to be returned:

jan 3 2007 - dec 31 2007
jan 3 2007-dec 31 2007
feb 3 2007-dec 31 2007
mar 3 2007-dec 31 2007
apr 3 2007-dec 31 2007
may 3 2007-dec 31 2007
jun 3 2007-dec 31 2007
jul 3 2007-
3 jul 2007 - aug 31 2007, 1 oct 2007-
3 aug 2007 - aug 31 2007, 1 oct 2007-31 dec 2007

but not these:

jan 2009
feb 2009


How can this be achieved with a regular expression in an oracle SQL query?
yelbowAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
käµfm³d 👽Connect With a Mentor Commented:
This seems to fit the above examples:
select * from parsetest where regexp_like (toparse, '(\d+\s+)?(j(an|u[nl])|feb|ma[ry]|apr|aug|sep|nov|dec)(\s*\d)?\s*\d{4}\s*-');

Open in new window

0
 
awking00Commented:
>>Given the data in the table created above, I want the following data to be returned:<<
jan 3 2007 - dec 31 2007
etc.
Is it intended for 2009 to be replaced by 2007 or is that just a typo?
0
 
yelbowAuthor Commented:
thanks!
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
yelbowAuthor Commented:
@awking00 - it was a typo, sorry!  Kaufmed's answer has done the trick though.
0
 
awking00Commented:
I tried responding to this earlier, but my firewall keeps restricting me from posting. Just want to point out that the next to last "\d" in kaufmed's solution needs to be modified to "\d+" or "\d\d?" to accommodate dates like jan 13 ... (i.e. two-digit days).
0
 
käµfm³d 👽Commented:
>>  kaufmed's solution needs to be modified to "\d+" or "\d\d?" ...

Yes it does. It seems I had a typo myself. Good eye  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.