Solved

Create columns in query based on string values in other column

Posted on 2013-12-03
13
436 Views
Last Modified: 2013-12-05
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
0
Comment
Question by:pm620wh
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39692302
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39692322
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39692325
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
 

Author Comment

by:pm620wh
ID: 39692567
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
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 39692814
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
 

Author Comment

by:pm620wh
ID: 39692946
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 39692977
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
 
LVL 34

Expert Comment

by:johnsone
ID: 39693005
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
 

Author Comment

by:pm620wh
ID: 39693052
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
 

Author Comment

by:pm620wh
ID: 39693657
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39693658
Please review the distribution of the points ;-)
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39695699
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now