Use REGEXP_SUBSTR in Oracle

You appear to want the sub-string after TOPSIDE\d+_ until the next . character.

You can do that by matching:

• Your prefix TOPSIDE\d+_
• Then non-greedily matching zero-or-more of any character .*?
• Then your suffix \.

If you only want the middle part then wrap it in a capturing group and extract the contents of that group:

SELECT REGEXP_SUBSTR(
         'L8_P0_TOPSIDE12_AA-MPS-K-PCE.JPG',
         'TOPSIDE\d+_(.*?)\.',
         1,    -- Start from the 1st character
         1,    -- Find the first match
         NULL, -- No flags
         1     -- Return the contents of the first capturing group
       ) AS match
FROM   DUAL;

Which outputs:

fiddle

Your pattern is almost correct, however you need to update it to

TOPSIDE\d+_[^.]+\.JPG

Changes

• [^.]+ - it will match one or more character other than dot, so until the extension
• \.JPG - match .JPG extension (together with the dot)

Your previous attempt (TOPSIDE\d+_[.]) would match underscore and dot after that only, as [.] is character class that would match the dot only. And only one dot.

If you need to extract string between the last underscore character and the next dot character you can do it without regexp - using just basic string functions like below:

WITH     --  S a m p l e    D a t a :
  tbl AS
    ( Select 1 as ID, 'L8_P0_TOPSIDE12_AA-MPS-K-PCE.JPG' as FNAME From Dual Union All
      Select 2 as ID, 'L10_P1_SIDE_X_12_BB-XYZ-K-PCE333.JPG' as FNAME From Dual
   )

--      S Q L : 
Select SubStr(FNAME, 
              Length(FNAME) - InStr(REVERSE(FNAME), '_', 1, 1) + 2,
              InStr(FNAME, '.', 1, 1) - (Length(FNAME) - InStr(REVERSE(FNAME), '_', 1, 1) + 2)
            ) As SUB_NAME
From   tbl

fiddle