REGEXP_SUBSTR(x, pattern [, start [, occurrence [, match_option]]]) gets a substring of x that matches pattern, which begins at the position specified by start. : REGEXP_SUBSTR « Regular Expressions Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Regular Expressions Functions
3. REGEXP_SUBSTR

The following example returns the substring that matches the regular expression l[[:alpha:]] {4} using REGEXP_SUBSTR():

SQL>
SQL> SELECT REGEXP_SUBSTR('abcedfghijklumnoprstuvwxyzabcedfghijklumnoprstuvwxyzabcedfghijklumnoprstuvwxyz', 'l[[:alpha:]]{4}') AS result
  2  FROM dual;

RESUL
-----
lumno

SQL>
SQL>