According to provided examples ...

replace(regexp_substr(column_name, 'PAGE#\d+'), 'PAGE#')

or something else.

with t as (
           select  'CODE#MacG Cop Cas#YEAR#1923-28#PAGE#34-0#' ,'CODE#MacG Cop Cas#YEAR#1923-28#PAGE#340a#' column_name
             from  dual
          )
select  regexp_substr(column_name, 'PAGE#(\d+)',1,1,null,1)
  from  t
/

REG
---
340

SQL> 

Thanks a lot I got a result set ..Checking for Various scenarios if my page comes like 'CODE#MacG Cop Cas#YEAR#1923-28#PAGE#34-0#' ,'CODE#MacG Cop Cas#YEAR#1923-28#PAGE#340a#' etc . Hope it works fine .

Do you understand why it is working fine?

Regards
Michel

6 guys trying to help the poster, 3 are posting hints with examples, other 3 are asking to follow forum guidelines,

lest the poster shouldn't be helped. Who is confusing whom?

Why not be clear at the 1st step.

BlackSwan posted forum guidelines almost after a lot of replies,

however, the poster repeatedly did not follow.

Had it been very clear in the beginning itself, then no point of so much of effort.

I appreciate the effort that was made to help the poster.

But, look in the end, the poster simply left the thread without even caring to tell what she learnt

or how she fixed the issue that she faced earlier.

It must be a win-win situation, where the helper should be satisfied in the end that the poster completely understood what was being explained.