Prakash Prakash - 4 months ago 20
SQL Question

Finding the maximum value from part of a string

I have the below data coming from DB. I would like to get the maximum of last digits after ".". For example data looks like this, where the last digits after last "." are 160410, 6, 16 etc.

I would like to get the "11.2.0.4.160419" output

11.2.0.4.160419
11.2.0.4.6
11.2.0.4.16
11.2.0.4.10
11.2.0.4.18
11.2.0.4.2
11.2.0.4.14
11.2.0.4.4
11.2.0.4.160119
11.2.0.4.3
11.2.0.4.15
11.2.0.4.9
11.2.0.4.17
11.2.0.4.8
11.2.0.4.5
11.2.0.4.7
11.2.0.4.1
11.2.0.4.151117
11.2.0.4.13
11.2.0.4.12
11.2.0.4.20
11.2.0.4.11
11.2.0.4.19


data before the "." are not same. It has various values. Infact the actual data is like this

DATABASE PATCH FOR EXADATA (JAN 2016 - 11.2.0.4.160119) : (22309110)
DATABASE PATCH FOR EXADATA (JAN 2016 - 11.2.0.4.16) : (22309111)
.
.


In this I am interested to get max of 160119.

-- Added

Sorry I am back again. We are looking for further where we need to get the result like this

11.2.0.4.160419

Meaning, the maximum of after "." , but when displaying display everything in between the parenthesis.

Actual data

'DATABASE PATCH FOR EXADATA (NOV 2015 - 11.2.0.4.151117)

DATABASE PATCH FOR EXADATA (APR2014 - 11.2.0.4.6) : (18293775)

DATABASE PATCH FOR EXADATA (APR2015 - 11.2.0.4.16) : (20449729)

desired output

(NOV 2015 - 11.2.0.4.151117)

I have this query working

with

inputs ( target_guid, description) as (

select t.target_guid, a.description from MGMT$OH_PATCH a, mgmt$oh_installed_targets oh,MGMT$TARGET_COMPONENTS c,MGMT$TARGET_FLAT_MEMBERS d, mgmt_targets t where t.target_type = 'oracle_dbmachine' and d.member_target_type = 'host' and d.aggregate_target_guid = t.target_guid and c.target_type = 'oracle_database' and c.host_name = d.member_target_name and a.host_name = c.host_name and a.target_guid = oh.oh_target_guid and oh.inst_target_type like '%database%' and a.description is not null and a.description like '%PATCH FOR EXADATA%' group by t.target_guid, a.description order by t.target_guid
)

select target_guid, max(to_number(regexp_substr(description, '.(\d*))', 1, 1, null, 1))) as version

from inputs group by target_guid;

with the output of

5DA0496CCCD42CA1099F1AD06216F3C0 160419

ED10DD7D4C62CEAA117E7B7E97883EC2 9

I need the output as

5DA0496CCCD42CA1099F1AD06216F3C0 11.2.0.4.160419
ED10DD7D4C62CEAA117E7B7E97883EC2 11.2.0.4.9

Can you please help?

Answer

If the assumptions I detailed in my Comment to your original question are correct, then something like this should work:

with
     inputs ( inp_str ) as (
       select 'DATABASE PATCH FOR EXADATA (JAN 2016 - 11.2.0.4.160119) : (22309110)' from dual union all
       select 'DATABASE PATCH FOR EXADATA (JAN 2016 - 11.2.0.4.16) : (22309111)' from dual
     )
select max(to_number(regexp_substr(inp_str, '.(\d*)\)', 1, 1, null, 1))) as max_something
from   inputs;

The select statement is really just the last two lines; the rest is for testing purposes. Replace inp_str with your actual column name, inputs with your table name, and max_something with your desired output column name.

Comments