Get middle value from a column name in Oracle

I have a column name as SAP_ID whose value comes as 'I-JK-SGAR-ENB-H021'. SO I want ENB` part from this column.

Below is the query:

select  SAP_ID, SITE_TYPE, SITEBACKHAUL, PRIORITY_SITE, RJ_COMPANY_CODE_1,  
BUSINESSRANKING, USAGE_TYPE, '1' AS STATUS
FROM R4G_OSP.ENODEB
 where SAP_ID IS NOT NULL;

>Solution :

Two usual options: regular expression or substr + instr combination.

Regular expression extracts 4th word from that string.

Substr + instr combination extracts string between 3rd and 4th - character.

SQL> with enodeb (sap_id) as
  2    (select 'I-JK-SGAR-ENB-H021' from dual)
  3  select regexp_substr(sap_id, '\w+', 1, 4) result_1,
  4         --
  5         substr(sap_id, instr(sap_id, '-', 1, 3) + 1,
  6                        instr(sap_id, '-', 1, 4) - instr(sap_id, '-', 1, 3) - 1
  7               ) result_2
  8  from enodeb;

RESULT_1   RESULT_2
---------- ----------
ENB        ENB

SQL>

As of usage type: use case expression, e.g.

select case regexp_substr(sap_id, '\w+', 1, 4)
            when 'ENB' then 42
            when 'COW' then 44
       end as usage_type
from ...