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?
>>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
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.
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).
Open in new window