Using Regular Expressions When Selecting Data

Regular expressions enable you to search for patterns in string data by using standardized syntax conventions. A regular expression can specify complex patterns of character sequences.

You specify a regular expression with metacharacters and literals. Metacharacters are operators that specify search algorithms. Literals are the characters for which you are searching.

The regular expression functions and conditions include REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, and REGEXP_SUBSTR. Example: Using Regular Expressions With the SQL SELECT Statement shows some examples of the use of the regular expression functions and conditions.

Using Regular Expressions With the SQL SELECT Statement

-- in the following example, the REGEXP_LIKE is used to select rows where
-- the value of job_id starts with ac, fi, mk, or st, 
-- then follows with _m, and ends with an or gr
-- the metacharacter | specifies OR
-- the 'i' option specifies case-insensitive matching
SELECT employee_id, job_id FROM employees 
   WHERE REGEXP_LIKE (job_id, '[ac|fi|mk|st]_m[an|gr]', 'i');

-- in the following example, REGEXP_REPLACE is used to replace 
-- phone numbers of the format "nnn.nnn.nnnn" with 
-- parentheses, spaces, and dashes to produce this format "(nnn) nnn-nnnn"
-- digits (0-9) are denoted with the metacharacter [:digit:]
-- the metacharacter {n} specifies a fixed number of occurrences
-- the \ is used an escape character so that the subsequent metacharacter 
-- in the expression is treated as a literal, such as \.; otherwise, the 
-- metacharacter . denotes any character in the expression
SELECT phone_number, REGEXP_REPLACE( phone_number,
 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') 
 "Phone Number" FROM employees;

-- in the following example, REGEXP_REPLACE is used to replace
-- phone numbers of the format "nnn.nnn.nnnn.nnnnnn"
-- with the format "+nnn-nn-nnnn-nnnnnn"
SELECT phone_number, REGEXP_REPLACE( phone_number,
 '([[:digit:]]{3})\.([[:digit:]]{2})\.([[:digit:]]{4})\.([[:digit:]]{6})',
 '+\1-\2-\3-\4') "Phone Number" FROM employees;

-- in the following example, REGEXP_SUBSTR returns the first substring
-- composed of one or more occurrences of digits and dashes
-- the metacharacter + specifies multiple occurrences in [[:digit:]-]+
SELECT street_address, REGEXP_SUBSTR(street_address, '[[:digit:]-]+', 1, 1) 
  "Street numbers" FROM locations;

-- in the following example, REGEXP_INSTR starts searching at the first character
-- in the string and returns the starting position (default) of the second
-- occurrence of one or more non-blank characters
-- REGEXP_INSTR returns 0 if not found
-- the metacharacter ^ denotes NOT, as in NOT space [^ ]
SELECT street_address, REGEXP_INSTR(street_address, '[^ ]+', 1, 1) 
  "Position of 2nd block" FROM locations;