Oracle Regular Expressions Pocket Reference

Name

^ (Caret) — Matches the beginning-of-line

Synopsis

Use the caret (^) to anchor an expression to the beginning of the source text, or to the beginning of a line within the source text.

By default, Oracle treats the entire source value as one line, so ^ matches only the very beginning of the source value:

SELECT REGEXP_SUBSTR(
    'one two three','^one ')
FROM dual;

one

If the text you’re looking for isn’t at the beginning of the source string, it won’t be matched. The following query returns NULL:

SELECT REGEXP_SUBSTR(
    'two one three','^one ')
FROM dual;

The caret is valid anywhere within an expression. For example, the following expression matches either 'One' or 'one', but in either case the word matched must come at the beginning of the string:

SELECT REGEXP_SUBSTR(
    'one two three','^One|^one')
FROM dual;

one

You can change Oracle’s default behavior to treat the source text as a set of “lines” delimited by newline characters. You do this using the 'm' match_parameter, as follows:

SELECT REGEXP_SUBSTR(
   'three two one' 
   || CHR(10) || 'one two three',
   '^one',1,1,'m')
FROM dual;

one

Because 'm' is used, the ^ anchors to the beginning of any line in the text, and the pattern '^one' matches the word 'one‘at the very beginning of the second line.

Be careful, though, that you don’t write an impossible expression such as 'one^one', which attempts to anchor the beginning of the string, or the beginning of a line within the string, to the middle of a value matched by the expression. You can ...