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

Question

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-

202002

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;

Answer 1

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:

202002

Demo on dbfiddle

Answer 2

One option is to use nested expressions: inner returns file extension and the date (that precedes that extension), and outer fetches date itself.

SQL> with test (col) as
  2    (select 'donkey_eats_pines_cones_201943_7771234_everyday_202002.txt' from dual)
  3  select regexp_substr(regexp_substr(col, '\d+.\w+$'), '\d+') result From test
  4  /

RESULT
------
202002

SQL>

Answer 3

check this

select reverse(split_part(reverse(r.r ), '.', 2)) from 
(
SELECT reverse(split_part(reverse('donkey_eats_pines_cones_20192301_7771234_everyday_202002.txt'), '_', 1)) as r
)as r

ANS : 202002