Extract Specific Set of data from a String in Oracle

Question

I have the string '1_A_B_C_D_E_1_2_3_4_5' and I am trying to extract the data 'A_B_C_D_E'. I am trying to remove the _1_2_3_4_5 & the 1_ portion from the string. Which is essentially the numeric portion in the string. any special characters after the last alphabet must also be removed. In this example the _ after the character E must also not be present.

and the Query I am trying is as below

SELECT 
REGEXP_SUBSTR('1_A_B_C_D_E_1_2_3_4_5','[^0-9]+',1,1) 
from dual

The Data I get from the above query is as below: -

_A_B_C_D_E_

I am trying to figure a way to remove the underscore towards the end. Any other way to approach this?

Answer 1

Assuming the "letters" come first and then the "digits", you could do something like this:

select regexp_substr('A_B_C_D_E_1_2_3_4_5','.*[A-Z]') from dual;

This will pull all the characters from the beginning of the string, up to the last upper-case letter in the string (.* is greedy, it will extend as far as possible while still allowing for one more upper-case letter to complete the match).