REGEX

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 match against the regular expression
regex
string
the regular expression to match
opt_args
string
optional arguments. Currently only supports "i", indicating case insensitive matching should be used
Returns: boolean

Example

query:

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

results:

sales_agent
Daniell Hammack
Versie Hillebrand
Markita Hansen
Kary Hendrixson
Niesha Huffines

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.