Oracle can do almost as much as Perl but it's not quite there yet!

This will pick out the date part of the filename.
<code>
string := '/oracle/export/MOCMMSP2/expdp_DEV2_D_FULL_20130131210003_01.dmp.gz';

select regexp_substr(
,      string,
,      '(\_)([[0-9]]+)(\_)'   --<-- expression with subexpressions
,      1                    --<-- starting position
,      1                    --<-- nth occurrence
,      'i'                  --<-- match parameter (ignore case)
,      2                    --<-- 11g: subexpression to return
,      )
from   dual
</code>

Pick out the file number. This expression will work with all these combinations. Useful for automating the addition of datafiles to the tablespace.
<code>
/cln/exp/ora_data2/clne/data2/ts_thaler_data_05.dbf
/cln/exp/ora_data2/clne/data2/ts_thaler_data_04.dbf
/cln/exp/ora_data2/clne/data2/ts_thaler_data_03.dbf
/cln/exp/ora_data2/clne/data2/ts_thaler_data_2.dbf
/cln/exp/ora_data2/clne/data3/ts_thaler_data100.dbf
/cln/exp/ora_data2/clne/data3/ts_thaler_data99.Dbf
/cln/exp/ora_data2/clne/data3/ts_thaler_data_98.DBF
/cln/exp/ora_data2/clne/data2/ts_thaler_data97.dbF
/cln/exp/ora_data2/clne/data2/ts_thaler_data_96.dbf
</code>
<code>
select regexp_substr(file_name,'(\d+)(\.dbf)',1,1,'i',1) file_number
from   dba_data_files
where  tablespace_name = 'TS_THALER_DATA'
/
</code>

=== References ===
[[http://www.oracle-developer.net/display.php?id=508|oracle-developer.net]]