Need help with regular expressions for manipulating data in a column of an Oracle database

Dear fellow developers:

I have a column in a table that resides in an Oracle database that is composed of text similar to the following:

Closing on Thursday, December 22, 2011 at 04:00 PM EST. Location....
Closing on Monday, December 26, 2011 at 05:00 PM EST. Location...
Closing on Wednesday, December 21, 2011 at 04:00 PM EST. Location...
...

What I need to do is create two regular expressions, one that parses the text, and selects ONLY the date (e.g. Thursday, December 22, 2011) from each row, and another regular expressions that captures ONLY the time (e.g. 04:00 PM EST).  

Ideally, what I would like to do is create a stored procedure that:

1.  Selects everything from this column
2.  Parses the text such that it separates the date portion from this block of text
3.  Parses the text such that it separates the time portion from this block of text
3.  RE-INSERT this date portion in the SAME column it currently resides in , replacing the text that is there
4.  INSERT the time portion into another column of the same table

The key here, is that I need to parse the text using regular expressions which I am not familiar with.  What I can say, is that despite the variable length, the content of the strings in each row is consistent.  Every row begins with, "Closing on...", and after the year, the word "at" appears before mentioning the time in the form "00:00 AM EST" followed be a period, with more text following.  Once I have the regular expression(s), I will then be incorporating it within an Oracle stored procedure.  I hope this question makes sense, and I will be more than happy to provide further clarification if necessary.

Thanks in advance to all who reply.
fsyedAsked:
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.

sdstuberCommented:
SELECT REGEXP_SUBSTR(str,
                     '(Mon|Tues|Wednes|Thurs|Fri|Satur|Sun)day, [A-Za-z]+ [0-9]{1,2}, [0-9]{4}'
                    )
           yourdate,
       REGEXP_SUBSTR(str, '[0-9]{2}:[0-9]{2} (AM|PM)') yourtime
  FROM yourtable
0
sdstuberCommented:
another option, doesn't require regexp's since you have hard "tags" to key from

SELECT SUBSTR(str, 12, INSTR(str, ' at ') - 12) yourdate,
       SUBSTR(str, INSTR(str, ' at ') + 4, 8) yourtime
  FROM yourtable;
0
sdstuberCommented:
oops,  just noticed I left out the timezone part in both...


the first one should have the time be...

REGEXP_SUBSTR(str, '[0-9]{2}:[0-9]{2} (AM|PM) [A-Z]+') yourtime


the second one should have the time be...

SUBSTR(str, INSTR(str, ' at ') + 4, 12) yourtime
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fsyedAuthor Commented:
Thanks so much for your quick reply, I'm very impressed!  It appears that you're providing one statement that captures both, date, and time (nice).  I was wondering how would I then insert these values into separate columns?  Just wondering.  FYI, you've definitely earned your points, I just want to know how to do this part so that I know where to go from this point.
0
sdstuberCommented:
a select statement can "feed" an insert statement


insert into sometable(x,y)
SELECT SUBSTR(str, 12, INSTR(str, ' at ') - 12) yourdate,
       SUBSTR(str, INSTR(str, ' at ') + 4, 12) yourtime
  FROM yourtable;
0
sdstuberCommented:
or you could load the values into variables then use those variables


i nsert into sometable(x,y) values(v_yourdate, v_yourtime);
0
fsyedAuthor Commented:
Thanks again for your prompt reply.  In your code block:

insert into sometable(x,y)
SELECT SUBSTR(str, 12, INSTR(str, ' at ') - 12) yourdate,
       SUBSTR(str, INSTR(str, ' at ') + 4, 12) yourtime
  FROM yourtable; 

Open in new window



If the column in my table is called "date" from which I am pulling the original block of text from (which has multiple columns), where do I mention it in your expression?  In your second example where you use variables, would it be:

INSERT into sometable(x,y) values(v_yourdate, v_yourtime); 
SELECT SUBSTR(str, 12, INSTR(str, ' at ') - 12) v_yourdate,
       SUBSTR(str, INSTR(str, ' at ') + 4, 12) v_yourtime
  FROM yourtable;

Open in new window


?  Just wondering.

Thanks again for your help!




0
awking00Commented:
I think by the original question you want to alter the table and add a time column.
Then update yourtable set time = SUBSTR(str, INSTR(str, ' at ') + 4, 12);
Then update yourtable set str = SUBSTR(str, 12, INSTR(str, ' at ') - 12);
0
fsyedAuthor Commented:
I apologize for not clarifying this, but there is already a time column in the table.  It's just that the values for both date and time are combined in a block of text that resides in the date column.
0
sdstuberCommented:
if your column is called "date" put "date" where I have "str"


for variables you could use the select first but that assumes you'd do something else with them.
if you won't do anything with the values except insert them, then just use the select as the insert feed

SELECT SUBSTR(str, 12, INSTR(str, ' at ') - 12) into v_yourdate,
       SUBSTR(str, INSTR(str, ' at ') + 4, 12) into v_yourtime
  FROM yourtable;


INSERT into sometable(x,y) values(v_yourdate, v_yourtime);
0
awking00Commented:
Then forget the add column -

update yourtable set time = SUBSTR(str, INSTR(str, ' at ') + 4, 12),
 date = SUBSTR(date, 12, INSTR(str, ' at ') - 12);
0
fsyedAuthor Commented:
Thanks again!  So what you're saying is

INSERT into sometable(x,y)
SELECT SUBSTR(str, 12, INSTR(str, ' at ') - 12) yourdate,
       SUBSTR(str, INSTR(str, ' at ') + 4, 12) yourtime
  FROM yourtable;

Open in new window



by itself would suffice, since I'm not manipulating the values after parsing them, and that 'str' in the expression represents the column I am pulling the original block of text from.  Are "yourdate", and "yourtime" variables in the expression?  If not, are they necessary?
0
awking00Commented:

>>3.  RE-INSERT this date portion in the SAME column it currently resides in , replacing the text that is there<<

RE-INSERT in the SAME column (I assumed named date) is an UPDATE -
update yourtable set date = SUBSTR(date, 12, INSTR(date, ' at ') - 12);

>>4.  INSERT the time portion into another column of the same table<<
This also an update -
update yourtable set timecolumn = SUBSTR(date, INSTR(date, ' at ') + 4, 12);

Or you can combine them both in one update statement -
update yourtable set time = SUBSTR(date, INSTR(date, ' at ') + 4, 12),
 date = SUBSTR(date, 12, INSTR(str, ' at ') - 12);



0

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
sdstuberCommented:
they are not necessary, they are aliases.


awking00 is correct in that if you are pulling from the same table and trying to modify the same rows you pulled from then you want to use update rather than insert.

the substr  or regexp will be the same either way though
0
fsyedAuthor Commented:
Thanks very much for all of your time and help in answering my question.  I really appreciate your help.  I wish that I could give both 500 points, but unfortunately that is not the case.  I gave 300 to sdstuber because he helped me from the very beginning, and gave 200 points to awking00 because he contributed a correct solution.  I honestly am sorry if either of you feel short-changed.

Thanks again to both of you for your help.

Take care.
Sincerely;
Fayyaz
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
Regular Expressions

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.