REGEXP_FIRSTSEARCH()

Scalar. Returns the first occurrence of a POSIX regular expression pattern found in a given string.

Syntax

REGEXP_FIRSTSEARCH( string, regex )

Parameters
string	A string.
regex	A POSIX regular expression pattern. This pattern is limited to the Perl syntax.

Data Types
Return	string	regex
String	String	String

Usage

If string does not contain a match for the pattern, or if the specified pattern is not a valid regular expression, the function returns NULL.

One or more subexpressions can be included in the pattern, each enclosed in parentheses. If string contains a match for the pattern, the function only returns the parts of the pattern specified by the first subexpression.

Examples

The following example uses the REGEXP_FIRSTSEARCH function to return the occurrence of the letters "Corp" at the end of the string in the column StockName, if present, or Null:

INSERT INTO OutStream
SELECT REGEXP_FIRSTSEARCH(Trades.StockName, "Corp$")
FROM Trades;

The following example extracts the components of a phone number. Only the components matching the subexpressions are returned. In the case of the third REGEXP_FIRSTSEARCH, only components matching the first subexpression are returned; the second subexpression is ignored.

INSERT INTO OutStream
SELECT 
    REGEXP_FIRSTSEARCH("(650) 210-3821", "\D*(\d{3})"),
    REGEXP_FIRSTSEARCH("(650) 210-3821", "\D*\d{3}\D*(\d{3})"),
    REGEXP_FIRSTSEARCH("(650) 210-3821","\D*\d{3}\D*\d{3}\D*(\d\d\d\d)")
FROM Entries;