REGEXP_SUBSTR Function

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.

Syntax

>>──REGEXP_SUBSTR───(──srcstr─┬─,pattern──────┬─)──────><
                              ├─,position─────┤
                              ├─,occurrence───┤
                              └─,modifier─────┘

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
      ,REGEXP_SUBSTR(cust_street_address,'^[[:digit:]]*')
FROM sh.customers 
WHERE REGEXP_LIKE(cust_main_phone_number
                 ,'^(\(?312\)?|\(?773\)?)')

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 constructing expressions.