REGEXP_EXTRACT

Syntax

STRING REGEXP_EXTRACT(STRING <source>, STRING <pattern>[, BIGINT <group_id>])

Overloads:

• REGEXP_EXTRACT(source, pattern) — returns the first capturing group (equivalent to group_id = 1)

• REGEXP_EXTRACT(source, pattern, group_id) — returns the capturing group at position group_id

Parameters

• source: Required. STRING. The string to search.

• pattern: Required. STRING constant or regular expression. The pattern to match against source. For syntax details, see Regular expression specifications.

• group_id: Optional. BIGINT. The index of the capturing group to return. Must be ≥ 0.

  • If omitted, defaults to 1 and returns the first capturing group.

  • If 0, returns the substring matching the entire pattern.

  • If a positive integer n, returns the nth capturing group.

Data is stored in UTF-8. The hexadecimal range for Chinese characters is [\x{4e00},\x{9fa5}].

Return value

Returns a STRING value. The following conditions apply:

• Returns NULL if source, pattern, or group_id is NULL.

• Returns an error if pattern is an empty string.

• Returns an error if pattern contains no capturing groups and group_id is not specified.

• Returns an error if group_id is not a BIGINT or is less than 0.

To change the behavior when pattern has no capturing groups and group_id is not specified, run SET odps.sql.bigquery.compatible=true; to enable BigQuery compatible mode. In this mode, the function returns the substring matching the entire pattern instead of an error.

Examples

• Example 1: Extract a substring from the string foothebar using the pattern foo(.*?)(bar). The following command is an example:

  -- Returns the because group_id is not specified.
  SELECT REGEXP_EXTRACT('foothebar', 'foo(.*?)(bar)');
  -- Returns foothebar because group_id is set to 0.
  SELECT REGEXP_EXTRACT('foothebar', 'foo(.*?)(bar)', 0);

• Example 2: Extract a substring from the string 8d99d8 using the pattern 8d(\\d+)d8. The following command is an example:

  -- Returns 99. When you submit a SQL statement for regular expression calculation on a MaxCompute client, use two backslashes (\\) as escape characters.
  SELECT REGEXP_EXTRACT('8d99d8', '8d(\\d+)d8');

• Example 3: Extract the Chinese characters and punctuation from 【Alibaba Cloud】aliyun. The following command is an example:

  -- Returns 【Alibaba Cloud】.
  SELECT REGEXP_EXTRACT('【Alibaba Cloud】aliyun', '([^\\x{00}-\\x{ff}]+)');

• Example 4: Extract the Chinese characters from 【Alibaba Cloud】aliyun. The following command is an example:

  -- Returns Alibaba Cloud.
  SELECT REGEXP_EXTRACT('【Alibaba Cloud】aliyun', '([\\x{4e00}-\\x{9fa5}]+)');

• Example 5: Extract the Chinese characters from 【Alibaba Cloud】aliyunAlibaba Cloud. You cannot use the REGEXP_EXTRACT function for this scenario. Instead, use the REGEXP_REPLACE function. The following command is an example:

  -- Returns 【Alibaba Cloud】Alibaba Cloud.
  SELECT REGEXP_REPLACE('【Alibaba Cloud】aliyunAlibaba Cloud','([\\x{00}-\\x{ff}])', '');

• Example 6: Use a pattern that contains no groups. The following commands are examples:

  • Scenario 1: If pattern contains no groups and group_id is not specified, an error is returned. In BigQuery compatible mode, the function returns the substring that matches the entire pattern.

    -- An error is returned. FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121065:Argument value out of range - Regex group count is 0, but the specified group index is 1
    SELECT REGEXP_EXTRACT('foothebar', 'thebar');
    
    -- Enable BigQuery compatible mode.
    SET odps.sql.bigquery.compatible=true;
    -- Returns thebar.
    SELECT REGEXP_EXTRACT('foothebar', 'thebar');

  • Scenario 2: If pattern contains no groups and group_id is set to 0, the function returns the substring that matches the entire pattern.

    -- Returns thebar.
    SELECT REGEXP_EXTRACT('foothebar', 'thebar',0);

Usage notes

Regex specification varies by data type edition:

For more information, see Hive-compatible data type edition, 1.0 data type edition, and 2.0 data type edition.

Related functions

REGEXP_EXTRACT is a string function. For other string functions, see String functions.