By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE your_table ( value ) AS
SELECT 'DUM_EI_AO_L_5864_Al Meena Tower' FROM DUAL UNION ALL
SELECT 'EI_AE_L_5864_Al radha Tower' FROM DUAL
2 rows affected
SELECT value,
SUBSTR( replaced_value, first_separator + 1, second_separator - first_separator - 1 )
AS second_term
FROM (
SELECT value,
replaced_value,
INSTR( replaced_value, '_', 1, 1 ) AS first_separator,
INSTR( replaced_value, '_', 1, 2 ) AS second_separator
FROM (
SELECT value,
REPLACE(
REPLACE(
value,
'PRJ_'
),
'DUM_'
) AS replaced_value
FROM your_table
)
)
| VALUE | SECOND_TERM |
|---|---|
| DUM_EI_AO_L_5864_Al Meena Tower | AO |
| EI_AE_L_5864_Al radha Tower | AE |
SELECT value,
REGEXP_SUBSTR( value, '(DUM_|PRJ_)?.*?_(.*?)_', 1, 1, NULL, 2 ) AS second_term
FROM your_table
| VALUE | SECOND_TERM |
|---|---|
| DUM_EI_AO_L_5864_Al Meena Tower | AO |
| EI_AE_L_5864_Al radha Tower | AE |