CODEWITHSUNDEEP
sqloracle-database
mnranees
mnranees

Reputation: 21

Need to select output using substr and instr function in oracle sql

My table contains below data

COLLECTION_TIMESTAM      VALUE DB_NAME                   PROPERTY_NAME                  PROPERTY_VALUE
------------------- ---------- -------------------------
2021-07-24 07:41:42      15670 ewp5s                     DataGuardStatus                Physical Standby
2021-07-24 07:46:42      15971 ewp5s                     DataGuardStatus                Physical Standby
2021-07-24 09:26:42      21953 ewp5s                     DataGuardStatus                Physical Standby
2021-07-24 09:31:42      22255 ewp5s                     DataGuardStatus                Physical Standby
2021-07-24 09:36:42      22556 ewp5s                     DataGuardStatus                Physical Standby
2021-07-20 08:13:05        554 opp201p.allstate.com      DataGuardStatus                Physical Standby
2021-07-22 01:57:18       5441 opp201p.allstate.com      DataGuardStatus                Physical Standby
2021-07-22 09:15:48        671 opp213s.allstate.com      DataGuardStatus                Physical Standby
2021-07-22 09:30:47        432 opp213s.allstate.com      DataGuardStatus                Physical Standby

My desired output is eliminating .allstate.com

COLLECTION_TIMESTAM      VALUE DB_NAME                   PROPERTY_NAME                  PROPERTY_VALUE
------------------- ---------- ------------------------- ------------------------------ ------------------------------
2021-07-24 07:41:42      15670 ewp5s                     DataGuardStatus                Physical Standby
2021-07-24 07:46:42      15971 ewp5s                     DataGuardStatus                Physical Standby
2021-07-24 09:26:42      21953 ewp5s                     DataGuardStatus                Physical Standby
2021-07-24 09:31:42      22255 ewp5s                     DataGuardStatus                Physical Standby
2021-07-24 09:36:42      22556 ewp5s                     DataGuardStatus                Physical Standby
2021-07-20 08:13:05        554 opp201p                   DataGuardStatus                Physical Standby
2021-07-22 01:57:18       5441 opp201p                   DataGuardStatus                Physical Standby
2021-07-22 09:15:48        671 opp213s                   DataGuardStatus                Physical Standby
2021-07-22 09:30:47        432 opp213s                   DataGuardStatus                Physical Standby

If i use below query its returning blanks in DB_NAME column. Can anyone advice?

select 
    collection_timestamp
   ,VALUE
   ,substr(target_name,1,instr(target_name,'.',1,1)- 1) db_name
   ,PROPERTY_NAME
   ,PROPERTY_VALUE  
FROM sysman.mgmt_metrics_raw;

COLLECTION_TIMESTAM      VALUE DB_NAME                   PROPERTY_NAME                  PROPERTY_VALUE
------------------- ---------- ------------------------- ------------------------------ ------------------------------
2021-07-24 07:41:42      15670                           DataGuardStatus                Physical Standby
2021-07-24 07:46:42      15971                           DataGuardStatus                Physical Standby
2021-07-24 09:26:42      21953                           DataGuardStatus                Physical Standby
2021-07-24 09:31:42      22255                           DataGuardStatus                Physical Standby
2021-07-24 09:36:42      22556                           DataGuardStatus                Physical Standby
2021-07-20 08:13:05        554 opp201p                   DataGuardStatus                Physical Standby
2021-07-22 01:57:18       5441 opp201p                   DataGuardStatus                Physical Standby
2021-07-22 09:15:48        671 opp213s                   DataGuardStatus                Physical Standby
2021-07-22 09:30:47        432 opp213s                   DataGuardStatus                Physical Standby

Upvotes: 0

Views: 85

Answers (2)

user5683823
user5683823

Reputation:

Concatenate a period to the first argument of instr, like so:

with
  tbl (target_name) as (
    select 'ewp5s'                from dual union all
    select 'opp201p.allstate.com' from dual
  )
select substr(target_name, 1, instr(target_name || '.', '.') - 1) as db_name
from   tbl
;

DB_NAME 
--------
ewp5s
opp201p

This should be much faster than any solution using regular expressions.

In the call to instr, note that the third and fourth arguments are 1 by default, so you don't need to give them explicitly (although there's nothing wrong with giving them).

Upvotes: 0

persian-theme
persian-theme

Reputation: 6638

Use case. The following query produces the desired result.

select 
    collection_timestamp
   ,VALUE
   ,case when instr(target_name,'.') == 0 then target_name
    else substr(target_name,1,instr(target_name,'.') - 1) end as db_name
   ,PROPERTY_NAME
   ,PROPERTY_VALUE  
FROM sysman.mgmt_metrics_raw;

Upvotes: 1

Related Questions