fsyed
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again for your prompt reply. In your code block:
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:
? Just wondering.
Thanks again for your help!
insert into sometable(x,y)
SELECT SUBSTR(str, 12, INSTR(str, ' at ') - 12) yourdate,
SUBSTR(str, INSTR(str, ' at ') + 4, 12) yourtime
FROM yourtable; 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;? Just wondering.
Thanks again for your help!
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);
Then update yourtable set time = SUBSTR(str, INSTR(str, ' at ') + 4, 12);
Then update yourtable set str = SUBSTR(str, 12, INSTR(str, ' at ') - 12);
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then forget the add column -
update yourtable set time = SUBSTR(str, INSTR(str, ' at ') + 4, 12),
date = SUBSTR(date, 12, INSTR(str, ' at ') - 12);
update yourtable set time = SUBSTR(str, INSTR(str, ' at ') + 4, 12),
date = SUBSTR(date, 12, INSTR(str, ' at ') - 12);
ASKER
Thanks again! So what you're saying is
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?
INSERT into sometable(x,y)
SELECT SUBSTR(str, 12, INSTR(str, ' at ') - 12) yourdate,
SUBSTR(str, INSTR(str, ' at ') + 4, 12) yourtime
FROM yourtable;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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
Thanks again to both of you for your help.
Take care.
Sincerely;
Fayyaz
ASKER