| 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 5results:
| 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.