REGEXP_CONTAINS string function - MaxCompute - Alibaba Cloud Documentation Center

The REGEXP_CONTAINS function checks whether a string contains content that matches a specified string or regular expression.

Syntax

BOOLEAN REGEXP_CONTAINS(STRING <source>, STRING <pattern>)

Parameters

source: Required. A value of the STRING type. This parameter specifies the string to be searched.
pattern: Required. A constant or regular expression of the STRING type.
Note
Use ^ (start of line) and $ (end of line) to search for a full match. Because operators in regular expressions have precedence, enclose all content between ^ and $ in parentheses to ensure the matching logic is correct.

Return value

Returns a BOOLEAN value. The following rules apply:

If the source value matches the pattern, the function returns `true`. Otherwise, it returns `false`.
The function returns an error if source or pattern is not a `STRING` or is invalid.
If source or pattern is NULL, the function returns NULL.
If the pattern is an empty string, the function returns true.

Examples

Example 1: 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;

The following result is returned:

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

Example 2: Use ^ (start of line) and $ (end of line) to search for a full match.

Because operators in regular expressions have precedence, enclose all content between ^ and $ in parentheses to ensure the matching logic is correct.

The regular expression produces the correct result without using parentheses.

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;

The following result is returned:

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

The lack of parentheses in the regular expression affects the output.

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;

The following result is returned:

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

Example 3: Use an empty string as the pattern.

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

Related functions

The REGEXP_CONTAINS function is a string function. For more information about functions for searching strings or transforming string formats, see String Functions Overview.