REGEXP_EXTRACT

A string function used in search operations for sophisticated pattern matching including repetition and alternation. For more information on the Java format for regular expressions see: https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html.

Parameter Type Description
input
string
the string to search for strings matching the regular expression.
regex
string
the regular expression to match
group
integer
an optional regular expression group number, defining which portion of the matching string will be returned
Returns: string

Example

query:

SELECT sales_teams.sales_agent
  FROM sales_teams
 WHERE REGEXP_EXTRACT(sales_agent, "(.*) (H.*)")

results:

sales_agent
Daniell Hammack
Versie Hillebrand
Markita Hansen
Kary Hendrixson
Niesha Huffines

query:

SELECT REGEXP_EXTRACT(sales_agent, "(.*) (.*)", 2) as last_name
  FROM sales_teams
 ORDER BY last_name
 LIMIT 5

results:

last_name
Anderson
Ascencio
Berthelot
Bicknell
Cantrell

The second argument in the REGEX function is written in the standard Java regular expression format and is case sensitive. In a standard Java regular expression the . stands as a wildcard for any one character, and the * means to repeat whatever came before it any number of times. In a .* regular expression, the Java single wildcard character is repeated, effectively making the .* regular expression operate the same way as the * wildcard does elsewhere in SQL.