asked on

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

Alex [***Alex140181***]

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

Geert G

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

Geert G

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;

pm620wh

ASKER

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

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pm620wh

ASKER

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.

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

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

pm620wh

ASKER

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.

pm620wh

ASKER

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

Alex [***Alex140181***]

Please review the distribution of the points ;-)

Geert G

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