Extract all Dates from a Comments Field that has multiple Dates In It

I have a Oracle table (not mine) that puts all below comments in 1 field called RCOMMENTS in a table called INVOICETABLE. If a new comment is entered it will be added to the beginning of this field. The example below is exactly how the field looks. Would there be way to read this field and only extract all the dates and times (7/3/17 6:12 PM, 6/30/17 4:07 PM etc.) ignoring the comments associated to each date and time ?  Also, I would like the dates to be in descending order, if possible.
This is a Oracle table and the field type is CLOB. If this is possible, my idea is to extract these dates and insert them put them into a SQL table (using OpenQuery)  called InvoiceDates and accompany the date records with a customer number that is in the INVOICETABLE.

Now, I am not allowed to create tables in the Oracle DB. I can only read Oracle tables and do open queries to put the records I want into SQL tables. I then use the SQL tables to create SSRS reports. I occasionally have to go to Oracle tables for data.  


7/3/17 6:12 PM 164830
C.Pi(NF-AR): Received an email from Calvin advising that they just received their proposed terms on June 26, 2017 and are in the process of reviewing whether they can come to an agreement on the rental rate.
 
6/30/17 4:07 PM 164830
C.Pi(NF-AR): Sent an email to  advising that we issued an invoice to the customer, but the new lease agreement has not been singed
 
6/30/17 3:58 PM 164830
C.pinto(NF-AR): Correction to my previous entry, corresponding with help.
 
6/30/17 3:55 PM 164830
C.Pinto(NF-AR): Received an email from advising that , real estate manager,  is corresponding with help.
 
6/29/17 11:47 AM 164830
C.Pinto(NF-AR): Received an email from , admin assistance for real estate, () advising that they have not received the invoice for payment. I advised her it was sent via e-bill to AP and opened by them. I provided her with a copy and requested if it was approved by them.
 
6/28/17 12:19 PM 164830
C.Po(NF-AR): Sent an email to requesting if there are any questions or concerns regarding the invoice. If not, I inquired when it will be approved and submitted to finance.
 
6/19/17 3:00 PM 769400
SSmith (CN AR): Invoice sent via E-Bill on Jun14th; Opened Jun15th.
thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
If you are going to insert the data into another table, don't bother with the order on select.  It won't matter.

This may not work for ALL rows if the insert a date in that format inside the comments but it should give you something to start with:
select to_date(regexp_substr(rcomments,'[0-9]{1,2}/[0-9]{1,2}/[0-9]{1,2} [0-9]{1,2}:[0-9]{2} (AM|PM)',1,level),'MM/DD/YYYY HH:MI AM') myDate
from tab1 connect by level <= regexp_count(rcomments,chr(10)||'[0-9]')
;

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexander Eßer [Alex140181]Software DeveloperCommented:
Try REGEXP_SUBSTR ;-)
I'm sure, you can adjust it, so that it suits your need...

select regexp_substr(a.rcomment, '\d{1,2}\/\d{1,2}\/\d{1,2}\s\d{1,2}:\d{2}\s(AM|PM)', 1, level)
  from (select '7/3/17 6:12 PM 164830
C.Pi(NF-AR): Received an email from Calvin advising that they just received their proposed terms on June 26, 2017 and are in the process of reviewing whether they can come to an agreement on the rental rate.
 
6/30/17 4:07 PM 164830
C.Pi(NF-AR): Sent an email to  advising that we issued an invoice to the customer, but the new lease agreement has not been singed
 
6/30/17 3:58 PM 164830
C.pinto(NF-AR): Correction to my previous entry, corresponding with help.
 
6/30/17 3:55 PM 164830
C.Pinto(NF-AR): Received an email from advising that , real estate manager,  is corresponding with help.
 
6/29/17 11:47 AM 164830
C.Pinto(NF-AR): Received an email from , admin assistance for real estate, () advising that they have not received the invoice for payment. I advised her it was sent via e-bill to AP and opened by them. I provided her with a copy and requested if it was approved by them.
 
6/28/17 12:19 PM 164830
C.Po(NF-AR): Sent an email to requesting if there are any questions or concerns regarding the invoice. If not, I inquired when it will be approved and submitted to finance.
 
6/19/17 3:00 PM 769400
SSmith (CN AR): Invoice sent via E-Bill on Jun14th; Opened Jun15th. ' as rcomment
           from dual) a
connect by level <= regexp_count(a.rcomment, '\d{1,2}\/\d{1,2}\/\d{1,2}\s\d{1,2}:\d{2}\s(AM|PM)');

Open in new window

1
thayduckProgrammer AnalystAuthor Commented:
Thanks, both solutions helped me out. Got what I needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.