Oracle Regular Expressions Pocket Reference

Name

Vertical Bar (|) — Delimits alternative possibilities

Synopsis

The vertical bar (|) is known as the alternation operator. It delimits, or separates, alternative subexpressions that are equally acceptable.

For example, the expression in the following query extracts the name of a fruit from a sentence. In this example the fruit is 'apple', but any of the three listed fruits: 'apple', 'apricot', or 'orange' is equally acceptable as a match:

SELECT REGEXP_SUBSTR(
   'An apple a day keeps the doctor away.',
   'apple|apricot|orange')
FROM dual;

apple

It’s usually wise to constrain your alternations using parentheses. For example, to modify the previous example to return the entire string, you could use:

SELECT REGEXP_SUBSTR(
   'An apple a day keeps the doctor away.',
   'An apple a day keeps the doctor away.' ||
   '|An apricot a day keeps the doctor away.' ||
   '|An orange a day keeps the doctor away.')
FROM dual;

This solution works, but it’s painfully repetitive and does not scale well. If there were two words that could change in each sentence, and if each word had three possibilities, you’d need to write 3 × 3=9 alternate versions of the sentence. The following approach is much better, and easier:

SELECT REGEXP_SUBSTR(
   'An apple a day keeps the doctor away.',
   'An (apple|apricot|orange) a day ' || 
   'keeps the doctor away.')
FROM dual;

By constraining the alternation to just that part of the text that can vary, we eliminated the need to repeat the text that stays the same.

Note

An expression such as ...