How to get file name without extension with using Regular Expressions

Question

I have a field with following values, now i want to extract only those rows with "xyz" in the field value mentioned below, can you please help?

        Mydata_xyz_aug21


        Mydata2_zzz_aug22


        Mydata3_xyz_aug33

One more requirement

I want to extract only "aIBM_MyProjectFile" from following string below, can you please help me with this?

finaldata/mydata/aIBM_MyProjectFile.exe.ld

I've tried this but it didn't work.

select 
regexp_substr('FinalProject/MyProject/aIBM_MyProjectFile.exe.ld','([^/]*)[\.]') exp 
from dual;

Answer 1

To extract substrings between the first pair of underscores, you need to use

regexp_substr('Mydata_xyz_aug21','_([^_]+)_', 1, 1, NULL, 1)

To get the file name without the extension, you need

regexp_substr('FinalProject/MyProject/aIBM_MyProjectFile.exe.ld','.*/([^.]+)', 1, 1, NULL, 1)

Note that each regex contains a capturing group (a pattern inside (...)) and this value is accessed with the last 1 argument to the regexp_substr function.

The _([^_]+)_ pattern finds the first _, then places 1 or more chars other than _ into Group 1 and then matches another _.

The .*/([^.]+) pattern matches the whole text up to the last /, then captures 1 or more chars other than . into Group 1 using ([^.]+).