REGEXP_SUBSTR

REGEXP_SUBSTR searches a string for a pattern and returns the matching string.

Return Value

CHAR | NCHAR | VARCHAR2 | NVARCHAR

Syntax

REGEXP_SUBSTR(source_char, pattern
              [, position
                 [, occurrence
                    [, match_parameter ]
                 ]
              ]
             )

Arguments

source_char is the text expression that is searched.

pattern is the text expression to search for. It is usually a text literal and can contain up to 512 bytes. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, "Oracle Regular Expression Support."

position is a nonzero integer indicating the character of source_char where the function begins the search. When position is negative, then the function counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the first character of source_char.

occurrence is an integer indicating which occurrence of pattern the function should search for. The value of occurrence must be positive. The default values of occurrence is 1, meaning the function searches for the first occurrence of pattern.

match_parameter is a text expression that lets you change the default matching behavior of the function. You can specify one or more of the following values:

c: Case-sensitive matching.
i: Case-insensitive matching.
m: Treat the source string as multiple lines. The function interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
n: New-line character is among the characters matched by a period (the wildcard character). By default, it is not.
x: Ignore whitespace characters.

Examples

REGEXP_SUBSTR('7 W 96th St, New York, NEW YORK', 'new york', 10, 2, 'i') starts searching at the tenth character and matches NEW YORK in a case-insensitive match.

REGEXP_SUBSTR('parsley, sage, rosemary, thyme', 's[^,]+e', 1, 2) starts searching at the first character and matches the second substring consisting of the letter s, any number of characters that are not commas, and the letter e. In this example, the function returns the value sage.