Create columns in query based on string values in other column

Hi

I'm trying to create a view that adds two additional columns based on a string value in another column.  I need to extract from the string a file name and a date/time value.

Representative samples of the string is below:

505_247703b0bab8_2012-11-14-16_32_12_068.00506
505_247703b0bab8_2012-11-14-16_32_12_068.00506
505_247703b0bab8_2012-11-14-16_32_12_068.00506
505_247703b0bab8_2012-11-14-16_32_12_068.00506

Note:  The separator in the string is the underscore "_"


I want to create two new columns in the view, one called filename and one called DateLastUploaded.

Using the "_" (underscore) as the separator, I want to extract the filename to one column and the date and time to another.  Using the above as a quick example, we would end up with two columns looking like this:

FILENAME                           DATELASTUPLOADED
--------------                           ------------------------------
505_247703b0bab8          14/11/2012 16:32:12
505_247703b0bab8          14/11/2012 16:32:12
505_247703b0bab8          14/11/2012 16:32:12
505_247703b0bab8          14/11/2012 16:32:12

Attached to this question is some sample data.  If you create a table from the sample data called HEALTHCHECK, the column containing the existing string is called LAST_DATA_FILENAME.

Having created the two new columns, I need to use their values in the HAVING clause e.g.

Select ..... (logic to select all existing columns and create two new columns called FILENAME AND LASTUPLOADDATE)
From HEALTHCHECK
GROUP BY  ..... To remove duplicates ....
HAVING MAX (HEALTHCHECK.DATELASTUPLOADED) < SYSDATE  - 0.5

Any help would be much appreciated.

Kind regards
pm620whAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
There is also the substr method of doing it

SELECT Substr(last_data_filename, 1, Instr(last_data_filename, '_', 1, 2) - 1) 
       FILENAME 
       , 
       To_date(Substr(last_data_filename, Instr(last_data_filename, '_', 
                                          1, 2) + 1, 
               Instr( 
                       last_data_filename, '_', 1, 5) - 
               Instr(last_data_filename, '_', 1, 2) 
                       - 1), 'yyyy-mm-dd-hh24:mi:ss') 
       DATELASTUPLOADED 
FROM   healthcheck; 

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
As long as the size doesn't vary, you could use something like this:
select substr(<your_col>,1,16) filename,
       to_date(substr(<your_col>,18,19), 'yyyy-mm-dd-hh24_mi_ss') datelastuploaded
from ....

Open in new window

0
 
Geert GOracle dbaCommented:
can't see attachment

you can extract the filename and date with regexp_substr and the subexpression with oracle 11

select 
regexp_substr(yourcolumn, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 1) filename, 
regexp_substr(yourcolumn, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 2) DATELASTUPLOADED
from table;

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Geert GOracle dbaCommented:
if you want it formatted as a date

select
regexp_substr(yourcolumn, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 1) filename,
to_date(regexp_substr(yourcolumn, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 2), 'yyyy-mm-dd-hh24_mi_ss') DATELASTUPLOADED
from table;
0
 
pm620whAuthor Commented:
Sorry, I did attach it once but a careless click of the back key lost my entry and I forgot to re-attach it.

Here it is.
Sample-Data.xls
0
 
pm620whAuthor Commented:
Hi johnsone

This is perfect thank you as it converts the column to a date.

May I ask one more question?  

How may I used the new column DATELASTUPLOADED in the same query to filter the resulting dates (I need to find only those dates that are up to 12 hours old).

With my limited knowledge, I was going to create a view and then use another view to query it.  Would that be the best approach please?

Thank you - almost there.
0
 
awking00Connect With a Mentor Commented:
create view new_columns_vw as
select <desiredcolumns>,
substr(last_data_filename,1,instr(last_data_filename,'_',1,2) - 1) filename,
to_date(substr(last_data_filename,instr(last_data_filename,'_',1,2) + 1,19),'yyyy-mm-dd_hh24_mi_ss') datelastuploaded
from healthcheck
where to_date(substr(last_data_filename,instr(last_data_filename,'_',1,2) + 1,19),'yyyy-mm-dd_hh24_mi_ss') > sysdate - 0.5;

select * from new_columns_vw;
0
 
johnsoneSenior Oracle DBACommented:
To use the date in the where clause, you need to repeat the to_date part of the query in the where like this:

SELECT Substr(last_data_filename, 1, Instr(last_data_filename, '_', 1, 2) - 1) 
       FILENAME 
       , 
       To_date(Substr(last_data_filename, Instr(last_data_filename, '_', 
                                          1, 2) + 1, 
               Instr( 
                       last_data_filename, '_', 1, 5) - 
               Instr(last_data_filename, '_', 1, 2) 
                       - 1), 'yyyy-mm-dd-hh24:mi:ss') 
       DATELASTUPLOADED 
FROM   healthcheck 
WHERE  To_date(Substr(last_data_filename, Instr(last_data_filename, '_', 1, 2) + 
                                          1, 
                      Instr( 
                              last_data_filename, '_', 1, 5) - 
                              Instr(last_data_filename, '_', 1, 2) - 1), 
              'yyyy-mm-dd-hh24:mi:ss') >= SYSDATE - 0.5; 

Open in new window


If this is a query that is going to run often, I would recommend putting a function based index on that to_date part of the query.  Without it, you would be doing a full table scan every time you run the query.

Here is a link to some of the documentation on function based indexes -> http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11731
0
 
pm620whAuthor Commented:
Perfect!

I feel that the last two solutions jointly solved this issue for me as johnsone showed me how to convert he values to a date and awking00 took me over the line.

Thank you to everyone however for your guidance an input.  I've learnt loads today.
0
 
pm620whAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 300 points for johnsone's comment #a39692814
Assisted answer: 200 points for awking00's comment #a39692977
Assisted answer: 0 points for pm620wh's comment #a39693052

for the following reason:

Very quick replies and really useful.  Helped me loads

Thank you to everyone
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Please review the distribution of the points ;-)
0
 
Geert GOracle dbaCommented:
did you actually try my approach ?
perfectly valid for separating 1 column into multiple column in oracle 11 !

btw, it will work for any format of filename before the date

ah ... i see you added the attachment

let me modify it so it works with your column name and table name
select
regexp_substr(last_data_filename, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 1) filename,
to_date(regexp_substr(last_data_filename, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 2), 'yyyy-mm-dd-hh24_mi_ss') DATELASTUPLOADED
from healthcheck
 

Open in new window

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.