Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


The REGEXP_SUBSTR function searches a string for a substring of a specified pattern and returns that substring.

Return Value



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



The text expression for which the function searches.


The text expression for which the function searches. It is usually a text literal and can contain up to 512 bytes. The function interprets a period as a wildcard character that matches any character


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.


An integer indicating which occurrence of pattern the function should search for. The value of occurrence must be positive. The default value of occurrence is 1, which means that the function searches for the first occurrence of pattern.


A text expression that lets you change the default matching behavior of the function. You can specify one or more of the values shown in the following table.

Value Specifies
c Case-sensitive matching.
i Case-insensitive matching.
m Treat the source string as multiple lines. The function interprets ^ (caret) and $ (dollar sign) 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 A newline character is among the characters matched by a period (the wildcard character). By default, it is not.
x Ignores whitespace characters.


Example 8-67 Using REGEXP_SUBSTRING to search for a case-insensitive substring

The following statement

REGEXP_SUBSTR('7 W 96th St, New York, New York', 'new york', 10, 2, 'i') 

starts searching at the tenth character and matches the second instance of New York in a case-insensitive match.

Example 8-68 Using REGEXP_SUBSTRING to return a substring from a string enclosed in single quotes

The following statement

REGEXP_SUBSTR('parsley, sage, rosemary, thyme', ',[^,]+,', 1) 

matches the first substring enclosed in single quotes ('), and returns the value, sage,.