Regular Expressions

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

Select a piece out of a long 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

This will pick out the date part of the filename.

References

[oracle-developer.net]