regex expression where it can extract the number positioned at the semi end in the string

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=94771b6589b01526ad0cf6e5c4d01945

I need help in extracting the number substring from a file name
currently for file format - 'monkey_eats_mango_everyday_202002.txt' we are doing like this

select regexp_substr('monkey_eats_mango_everyday_202002.txt', '\d+') as parameter12a 
from dual;

result-

which in turn used in larger query to get the last date of this date like this

select to_char(last_day(to_date(regexp_substr('monkey_eats_mango_everyday_202002.txt', '\d+'),  'yyyymm')), 'yyyymmdd') as parameter 
from dual ;

result-

20200229

Now the file format has changed, so we have - 'donkey_eats_pines_cones_20192301_7771234_everyday_202002.txt'

In this file format there are numbers at other places like 201943_7771234 which can be dates or any random number, so I need regex expression which can extract 202002 from file format

select regexp_substr('donkey_eats_pines_cones_201943_7771234_everyday_202002.txt', '\d+') as parameter12a 
from dual;

Solution

You can use a \. to anchor your digits match to next to the period in the file name, and then use a capture group around the digits to get just the digits in the output, using the 6th parameter to REGEXP_SUBSTR to indicate that you only want group 1 in the output:

SELECT REGEXP_SUBSTR('donkey_eats_pines_cones_201943_7771234_everyday_202002.txt', '(\d+)\.', 1, 1, NULL, 1) AS parameter12a 
FROM dual;

Output:

Demo on dbfiddle