REGEXP_SUBSTR for portion of string

Question

I would like to get:

82961_01B04WZXQQSUGJ4YMRRT2A7TRHK_MR_2_1of1

from the following expression

LASTNAME_FIRSTNAME_82961_01B04WZXQQSUGJ4YMRRT2A7TRHK_MR_2_1of1

Does someone know how I can get this using regexp_substr ?

EDIT

Basically I have a field which has 7 sets each separated by _ . The string I gave is just one example. I wanted to retrieve everything after the second _ . There is no fixed character length so I can not use a substr function. Hence I was using regexp_substr. I was able to get away by using a simplified version

Select FILE_NAME, (  (REGEXP_SUBSTR(FILE_NAME,'[^_]+_',1,3)) || 
                      (REGEXP_SUBSTR(FILE_NAME,'[^_]+_',1,4)) || 
                      (REGEXP_SUBSTR(FILE_NAME,'[^_]+_',1,5)) ||
                      (REGEXP_SUBSTR(FILE_NAME,'[^_]+_',1,6)) || 
                      (REGEXP_SUBSTR(FILE_NAME,'[^_]+',1,7))  ) as RegExp
             from tbl

Here is some more data from the FILE_NAME field

LAST_FIRST_82961_01B04WZXQQSUGJ4YMRRT2A7TRHK_MR_2_1of1
SMITH_JOHN_82961_0130BPQX9QZN9G4P5RDTPA9HR4R_MR_1_1of1
LASTNAME_FIRSTNAME_99999_01V0MU4XUQK0Y24Y9RYTFA7W1CM_MR_3_1of1

Answer 1

From your Requirement, you can just go ahead and use the simple SUBSTRfunction. Its faster, and it addresses the simple need to remove the String LASTNAME_FIRSTNAME.

select  substr('LASTNAME_FIRSTNAME_82961_01B04WZXQQSUGJ4YMRRT2A7TRHK_MR_2_1of1', 20) data_string
from    dual;

Output:

data_string
-----------------
82961_01B04WZXQQSUGJ4YMRRT2A7TRHK_MR_2_1of1

Unless you have another underlying logic you need to address? Kindly clarify so i can edit the answer accordingly.

Answer 2

To get everything after the second underscore, you do not need regular expressions, but can use something like the following:

 select substr(FILE_NAME, instr(FILE_NAME, '_', 1, 2) +1 ) from tbl    

The instr returns the position of the second occurrence of '_', starting by the first character; the substr simply gets everything starting from the position given by instr + 1