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