I want to select Directory name from the datafile name in V$datafile view.
SQL> select name from v$datafile where file#=1;
SQL> /misc01/u50/ORACLE/RMANP1/system_01_RMANP1.dbf

I want the result as

I cannot give
SQL> select replace(name,'system_01_RMANP1.dbf','') from v$datafile where file#=1;

Because RMANP1 is ORACLE_SID and I need to run the sql in a common script for 100 databases so it should be generic solution for all databases.

I tried
SQL> select replace(name,'system_01_%','')from v$datafile where file#=1;
SQL> /misc01/u50/ORACLE/RMANP1/system_01_RMANP1.dbf

but it is not working and giving me the whole datafile name.

Please Help.