dbawiki

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.

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

Pick out the file number. This expression will work with all these combinations. Useful for automating the addition of datafiles to the tablespace.

/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

select regexp_substr(file_name,'(\d+)(\.dbf)',1,1,'i',1) file_number
from   dba_data_files
where  tablespace_name = 'TS_THALER_DATA'
/

References