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

Question

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

Do you need to remove .allstate.com, as you say in words, or do you need to remove the period and everything that comes after it, no matter what that is (as your query does, when there are periods in the string)? Or is allstate.com the only possible string following a period, if there is a period in the string at all? — user5683823
– user5683823, Commented Jul 25, 2021 at 20:38

persian-theme · Accepted Answer · 2021-07-25 17:02:46Z

1

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;

answered Jul 25, 2021 at 17:02

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2021-07-25 20:43:43Z

0

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).

answered Jul 25, 2021 at 20:43

user5683823

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related