MaxCompute:REGEXP_REPLACE

Syntax

string regexp_replace(string <source>, string <pattern>, string <replace_string>[, bigint <occurrence>])

Parameters

• source: required. A value of the STRING type. This parameter specifies the string that you want to replace.

• pattern: required. A constant of the STRING type or a regular expression. This parameter specifies the pattern that the specified substring must match. For more information about regular expressions, see Regular expressions. If pattern is an empty string, an error is returned.

• replace_string: required. A value of the STRING type. The value is used to replace the string that matches the pattern.

  Note

  • If the replace_string is an empty string, the function returns after removing the string matching the pattern.

  • The value of replace_string can contain a backward reference \n. If the value of replace_string contains a backward reference, a substring that matches the nth capturing group specified by pattern is inserted. n is a digit ranging from 1 to 9. If the value of replace_string contains the backward reference \0, the substring that matches the entire pattern is inserted. Backslashes (\) must be escaped. For example, if the value of replace_string contains the backward reference \1, the backslash (\) of the backward reference must be escaped and the backward reference \1 is expressed as (\\1). You can also use the raw string R'(\1)' to express the backward reference \1.

• occurrence: optional. A constant of the BIGINT type, which must be greater than or equal to 0. The value of this parameter indicates that the string that matches the specified pattern at the nth occurrence specified by occurrence is replaced with the string specified by replace_string. If this parameter is set to 0, all the substrings that match the specified pattern are replaced. If this parameter is of another data type or is less than 0, an error is returned. Default value: 0.

Return value

Examples

• Example 1: Use (\\1)\\2-\\3 to replace all the substrings that match ([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4}) in the 123.456.7890 string. Sample statements:

  -- The return value is (123)456-7890. 
  select regexp_replace('123.456.7890', '([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})',
  '(\\1)\\2-\\3', 0);

• Example 2: Replace the substring that matches the specified pattern in the abcd string. Sample statements:

  -- The return value is a b c d. 
  select regexp_replace('abcd', '(.)', '\\1 ', 0);
  -- The return value is a bcd. 
  select regexp_replace('abcd', '(.)', '\\1 ', 1);
  -- The return value is abcd. 
  select regexp_replace('abcd', '(.)', '\\2', 1);

• Example 3: The data of the url column in the table url_set is in the www.simple@xxx.com format and xxx in each row is different. Replace all the content after www in the url column. Sample statements:

  -- The return value is wwwtest. 
  select regexp_replace(url,'(www)(.*)','wwwtest',0) from url_set;

• Example 4: An input parameter is set to null. Sample statements:

  -- The return value is null. 
  select regexp_replace('abcd', '(.)', null, 0);

• Example 5: The group that you want to reference does not exist. Sample statements:

  -- Only one group is defined in the pattern, and the referenced group does not exist. 
  -- We recommend that you do not use this function in this way. The result of referencing a nonexistent group is not defined. 
  regexp_replace("abcd", "(.*)(.)$", "\\2", 0) = "d"
  -- The referenced group is defined in the pattern. Therefore, "\1" references a nonexistent group. 
  -- We recommend that you do not use this function in this way. The result of referencing a nonexistent group is not defined. 
  regexp_replace("abcd", "a", "\\1", 0) = "bcd"

Related functions

REGEXP_REPLACE is a string function. For more information about functions related to string searches and conversion, see String functions.