MaxCompute:REGEXP_CONTAINS

Validate an email address

SELECT
  'foo@example.com' AS email,
  REGEXP_CONTAINS('foo@example.com', '@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+') AS is_valid;

Result:

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
+-----------------+----------+

Match the entire string with ^ and $

Use ^ (start of line) and $ (end of line) to search for a full match. When using ^ and $ with alternation (|), wrap the alternatives in parentheses. Without parentheses, operator precedence applies only to the nearest term, not the entire expression — which can produce unexpected results.

The following example uses two inputs to show the difference.

Input: a@foo.com

SELECT
  'a@foo.com' AS email,
  REGEXP_CONTAINS('a@foo.com', '^([\\w.+-]+@foo\\.com|[\\w.+-]+@bar\\.org)$') AS valid_email_address,
  REGEXP_CONTAINS('a@foo.com', '^[\\w.+-]+@foo\\.com|[\\w.+-]+@bar\\.org$') AS without_parentheses;

Result:

+------------+---------------------+---------------------+
| email      | valid_email_address | without_parentheses |
+------------+---------------------+---------------------+
| a@foo.com  | true                | true                |
+------------+---------------------+---------------------+

Both patterns return true for a@foo.com — the difference appears with the second input.

Input: !b@bar.org

SELECT
  '!b@bar.org' AS email,
  REGEXP_CONTAINS('!b@bar.org', '^([\\w.+-]+@foo\\.com|[\\w.+-]+@bar\\.org)$') AS valid_email_address,
  REGEXP_CONTAINS('!b@bar.org', '^[\\w.+-]+@foo\\.com|[\\w.+-]+@bar\\.org$') AS without_parentheses;

Result:

+------------+---------------------+---------------------+
| email      | valid_email_address | without_parentheses |
+------------+---------------------+---------------------+
| !b@bar.org | false               | true                |
+------------+---------------------+---------------------+

!b@bar.org starts with !, which is not a valid word character. With parentheses, ^ anchors the entire alternation and the result is correctly false. Without parentheses, ^ anchors only the first alternative ([\\w.+-]+@foo\\.com), so the second alternative ([\\w.+-]+@bar\\.org$) matches the suffix b@bar.org — returning true even though the full string is invalid.

Use an empty string as the pattern

-- Returns true.
WITH dummy AS(SELECT '' AS PATTERN) SELECT REGEXP_CONTAINS('ABC',PATTERN) FROM dummy;