Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table your_table (INVENTORY_STATUS_CODE, RJ_MAINTENANCE_ZONE_CODE, RJ_INTRACITY_LINK_ID) as
select 'IPL', 'INUEABDD01', 'ABDD_0102_U' from dual
union all
select 'IPL', 'INUEABDD01', 'ABDD_1037/' from dual
union all
select 'IPL', 'INUEABDD01', 'ABDD_3102' from dual
union all
select 'IPL', 'INUEABDD01', 'ABDD_4003' from dual
union all
select 'IPL', 'INUEABDD01', 'STHU_9032' from dual
union all
select 'IPL', 'INUEABDD01', 'EXTRA A/B' from dual
union all
select 'IPL', 'INUEABDD01', 'EXTRA_A/B' from dual
union all
select 'IPL', 'INUEABDD01', '/EXTRA_A' from dual
union all
select 'IPL', 'INUEABDD01', 'EXTRA_A/' from dual
9 rows affected

SELECT TO_CHAR(RJ_INTRACITY_LINK_ID) AS SPAN_ID, 
       TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS  MAINT_ZONE_CODE
  FROM your_table
 WHERE LENGTH(RJ_INTRACITY_LINK_ID) > 8 
   AND LENGTH(RJ_INTRACITY_LINK_ID) < 21            
   AND INVENTORY_STATUS_CODE = 'IPL' 
   AND RJ_MAINTENANCE_ZONE_CODE = 'INUEABDD01'
   AND NOT REGEXP_LIKE (RJ_INTRACITY_LINK_ID,'_(9|31|4|7|_____|U|[\/]$)','i')


SPAN_ID
MAINT_ZONE_CODE


ABDD_1037/
INUEABDD01

EXTRA A/B
INUEABDD01

EXTRA_A/B
INUEABDD01


select RJ_INTRACITY_LINK_ID,
  REGEXP_SUBSTR (RJ_INTRACITY_LINK_ID,'_(9|31|4|7|_____|U|[\/]$)') as your_match,
  REGEXP_SUBSTR (RJ_INTRACITY_LINK_ID,'_(9|31|4|7|_____|U|.*/$)') as my_match_1,
  REGEXP_SUBSTR (RJ_INTRACITY_LINK_ID,'(_(9|31|4|7|_____|U)|/)') as my_match_2
from your_table


RJ_INTRACITY_LINK_ID
YOUR_MATCH
MY_MATCH_1
MY_MATCH_2


ABDD_0102_U
_U
_U
_U

ABDD_1037/
null
_1037/
/

ABDD_3102
_31
_31
_31

ABDD_4003
_4
_4
_4

STHU_9032
_9
_9
_9

EXTRA A/B
null
null
/

EXTRA_A/B
null
null
/

/EXTRA_A
null
null
/

EXTRA_A/
null
_A/
/


SELECT TO_CHAR(RJ_INTRACITY_LINK_ID) AS SPAN_ID, 
       TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS  MAINT_ZONE_CODE
  FROM your_table
 WHERE LENGTH(RJ_INTRACITY_LINK_ID) > 8 
   AND LENGTH(RJ_INTRACITY_LINK_ID) < 21            
   AND INVENTORY_STATUS_CODE = 'IPL' 
   AND RJ_MAINTENANCE_ZONE_CODE = 'INUEABDD01'
   AND NOT REGEXP_LIKE (RJ_INTRACITY_LINK_ID,'_(9|31|4|7|_____|U|.*/$)','i')


SPAN_ID
MAINT_ZONE_CODE


EXTRA A/B
INUEABDD01

EXTRA_A/B
INUEABDD01


SELECT TO_CHAR(RJ_INTRACITY_LINK_ID) AS SPAN_ID, 
       TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS  MAINT_ZONE_CODE
  FROM your_table
 WHERE LENGTH(RJ_INTRACITY_LINK_ID) > 8 
   AND LENGTH(RJ_INTRACITY_LINK_ID) < 21            
   AND INVENTORY_STATUS_CODE = 'IPL' 
   AND RJ_MAINTENANCE_ZONE_CODE = 'INUEABDD01'
   AND NOT REGEXP_LIKE (RJ_INTRACITY_LINK_ID,'(_(9|31|4|7|_____|U)|/)','i')

RJ_INTRACITY_LINK_ID	YOUR_MATCH	MY_MATCH_1	MY_MATCH_2
ABDD_0102_U	_U	_U	_U
ABDD_1037/	null	_1037/	/
ABDD_3102	_31	_31	_31
ABDD_4003	_4	_4	_4
STHU_9032	_9	_9	_9
EXTRA A/B	null	null	/
EXTRA_A/B	null	null	/
/EXTRA_A	null	null	/
EXTRA_A/	null	_A/	/