The REGEXP_SUBSTR function returns the portion of the search string that matches the regular expression pattern. The parameter names below are those used in the package STANDARD, thus can be used in your PL/SQL programs with named notation calling style.
Click on each parameter in BLUE for an explanation of its use...
For the example, look in the sh.customers table in the standard sample schemas. The sample schemas are demonstration
objects that ship with Oracle 9i and later. They are installed in the seed database and are available for installation
in any other database. If you do not have the sample schemas installed in your database, you or your DBA can install
them using the Database Configuration Assistant or manually with the $ORACLE_HOME/demo/schema/mk_sample.sql script.
We are interested in the street number of the customers who are in Chicago. The street number is the leading digits
in the street address. A regular expression is used to match the leading numbers and the REGEXP_SUBSTR function to
extract this patter from the column..
SELECT cust_first_name, cust_last_name
In the example above, the street number was identified as the zero or more digit characters that appear at the
beginning of the search string. See Regular Expression Metacharacters for full details on