REGEXP_REPLACE

Syntax

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

Parameters

Return value

Returns a STRING value. The return value depends on the following conditions:

• If the referenced capturing group does not exist, the result is undefined.

• If replace_string is null and a match is found, null is returned.

• If replace_string is null but no match is found, the original string is returned.

• If source, pattern, or occurrence is null, null is returned.

Usage notes

Regex specification by data type version

The regex specification used by REGEXP_REPLACE depends on the data type version of your MaxCompute project:

• Hive-compatible data type versions: follows the Java regex specification.

• Data type versions 1.0 and 2.0: follows the MaxCompute specification.

Backreferences in `replace_string`

replace_string supports backreferences using \n syntax, where n is a digit from 1 to 9. A backreference inserts the substring matched by the nth capturing group in pattern. Using \0 inserts the substring matched by the entire pattern.

Backslashes must be escaped. To use the backreference \1, write it as \\1. Alternatively, use a raw string: R'(\1)'.

Examples

Remove matched substrings

Pass an empty string as replace_string to delete all matching substrings:

-- Remove the letter "a": returns "bcd"
select regexp_replace("abcd", "a", "", 0);

-- Remove all hyphens: returns "19700101"
select regexp_replace("1970-01-01", "-", "", 0);

-- Remove all digits: returns "abc"
select regexp_replace("a1b2c3", "[0-9]", "", 0);

Replace all occurrences

Set occurrence to 0 (or omit it) to replace every match:

-- Replace all "a" with "A": returns "Abcd"
select regexp_replace("abcd", "a", "A", 0);

Replace a specific occurrence

Set occurrence to a positive integer to replace only that occurrence. The following example replaces only the third digit:

-- Replace only the 3rd digit: returns "a1b2c"
select regexp_replace("a1b2c3", "[0-9]", "", 3);

Reformat a string using backreferences

Use capturing groups in pattern and backreferences in replace_string to rearrange parts of a string. The following example reformats a phone number from 123.456.7890 to (123)456-7890 by capturing three groups of digits and rearranging them:

-- Reformat phone number: returns "(123)456-7890"
select regexp_replace('123.456.7890', '([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})',
'(\\1)\\2-\\3', 0);

Insert a space after each character

Use a capturing group that matches any single character, then append a space in the replacement:

-- Insert a space after each character: returns "a b c d"
select regexp_replace('abcd', '(.)', '\\1 ', 0);

-- Insert a space after only the first character: returns "a bcd"
select regexp_replace('abcd', '(.)', '\\1 ', 1);

Extract the last character

Use a capturing group to isolate the last character and return only that:

-- Return only the last character: returns "d"
select regexp_replace("abcd", "(.*)(.)$", "\\2", 0);

Replace matched content with a fixed string

The following example replaces everything after www in each URL in the url_set table with the fixed string wwwtest. The data in the url column is in the format www.simple@xxx.com:

-- Replace the domain suffix: returns "wwwtest"
select regexp_replace(url,'(www)(.*)','wwwtest',0) from url_set;

Null input

If replace_string is null and the pattern matches, null is returned:

-- replace_string is null and pattern matches: returns null
select regexp_replace('abcd', '(.)', null, 0);

Related functions

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