Extract Date Along with Am or pm in oracle

I want to get the time pattern along with AM or PM from the given string Aaaaa_gggg_ne_A030_66788_Abcd_Oct_24_0329PM.csv

I tried the following:

Select regexp_substr(filename,'\d{4}',1,3)
From
(Select 'Aaaaa_gggg_ne_A030_66788_Abcd_Oct_24_0329PM.csv' filename from dual);

which only gives me the last number, e.g. 0329, but I need 0329PM.

Using this form of REGEXP_SUBSTR() will get what you need in one call. It returns the first group, which is the set of characters after the last underscore and before the literal period of 1 or more numbers followed by an A or P then an M.

with tbl(filename) as (
  Select 'Aaaaa_gggg_ne_A030_66788_Abcd_Oct_24_0329PM.csv'  
  from dual
)
select regexp_substr(filename, '_(\d+[AP]M)\.', 1, 1, NULL, 1) 
From tbl;

Actually, to tighten up the match you could make it case-insensitive and add the extension:

select regexp_substr(filename, '_(\d+[AP]M)\.csv', 1, 1, 'i', 1) 
From tbl;

Note if a match is not found NULL will be returned.

Nested substr is one option (if data always looks like this; you didn't say it doesn't):

SQL> with test (col) as
  2    (select 'Aaaaa_gggg_ne_A030_66788_Abcd_Oct_24_0329PM.csv' from dual)
  3  select substr(substr(col, -10), 1, 6) result from test
  4  /

RESULT
------
0329PM

SQL>

• the inner substr returns the last 10 characters (0329PM.csv)
• the outer substr returns the first 6 characters out of it (0329PM)

Or, using regular expressions:

SQL> with test (col) as
  2    (select 'Aaaaa_gggg_ne_A030_66788_Abcd_Oct_24_0329PM.csv' from dual)
  3  select regexp_substr(translate(col, '_.', '  '), '\S+',
  4                       1,
  5                       regexp_count(translate(col, '_.', '  '), '\S+') - 1
  6                      ) result
  7  from test;

RESULT
------
0329PM

SQL>

• line #3: translate replaces underlines and dots with a space
• line #4: start from the beginning
• line #5: return substring which is one before the last one