REGEXP_MATCH

The REGEXP_MATCH function evaluates a field or expression using Google RE2 regular expression.

Sample Usage

Syntax
REGEXP_MATCH(X, Y)

Parameters:

  • X - a field or expression to evaluate
  • Y - a regular expression

Notes

  • REGEXP_MATCH uses RE2-style regular expressions.
  • The REGEXP_MATCH function returns boolean values.
  • REGEXP_MATCH attempts to match the entire string contained in field_expression. For example, if field_expression is "ABC123":

    REGEXP_MATCH(field_expression, 'A') returns false.
    REGEXP_MATCH(field_expression, 'A.*') returns true.

  • Regular expressions are case-sensitive by default. You can make the match case-insensitive using the (?i) flag:

    REGEXP_MATCH(field_expression, '(?i)a.*') matches both "abc123" and "ABC123"

  • Escape special characters with 2 backslash characters:

    REGEXP_MATCH(MyField, "foo\\.bar") matches the literal period character.

Examples

Match if name begins with a letter:

REGEXP_MATCH(name, '[a-zA-Z].*')

 

Combine with CASE statement to create sales regions from country values:

CASE WHEN REGEXP_MATCH(country, "(USA|Canada|Mexico)") THEN "North America" WHEN     REGEXP_MATCH(country, "(England|France)" ) THEN "Europe" ELSE "Other" END

Use case-insensitive operator and logical operators to categorize ad campaigns:

CASE
  WHEN REGEXP_MATCH(Campaign, "((?i).*Recipies).*") OR REGEXP_MATCH(Campaign, "((?i).*Cooking).*")
  THEN "Recipies"

  WHEN REGEXP_MATCH(Campaign, "((?i).*Grilling).*")
  THEN "Seasonal"

  WHEN REGEXP_MATCH(Campaign, "((?i).*Phone).*") OR REGEXP_MATCH(Campaign, "((?i).*Tablet).*") OR     REGEXP_MATCH(Campaign, "((?i).*Console).*") OR REGEXP_MATCH(Campaign, "((?i).*Laptop).*") OR       REGEXP_MATCH(Campaign, "((?i).*Wearable).*")

  THEN "Electronics" 

  ELSE "Other" 
END
  

REGEXP_MATCH supports the NOT operator when used with CASE:

CASE WHEN REGEXP_MATCH(dimension1, 'A') AND NOT REGEXP_MATCH(dimension1, 'B') THEN 'C' ELSE 'D' END
Was this article helpful?
How can we improve it?