REPLACE

Replaces all occurrences of a substring specified by patt with repl (optional), or deletes this substring.

Syntax

REPLACE(expr, patt[, repl])

Arguments

expr: Sequence of characters or text column of data type STRING to be processed.

---

patt: Substring to be replaced. Note that it is not a regular expression pattern. If you want to use regular expression patterns instead, please use the REGEXP_SUBSTR function.

---

repl (optional): This value is used as a replacement for patt. If repl is omitted, specified as NULL, or an empty string, the function deletes all occurrences of patt in expr.

Return Values

The return value is a string after patt has been replaced by expr, of data type STRING.

Usage Notes

• If patt is NULL or does not occur in expr, expr is returned.
• Only occurrences in the original expr are considered. A patt that occurs in the return value is not removed/replaced.
• If expr is NULL, NULL is returned.

Example 1

select replace('Gaudeamus', 'au') as result1, 
       replace('Gaudeamus', 'au', 'AU') as result2, 
       replace('Gaudeamus', 'au', NULL) as result3, 
       replace(NULL, NULL, 'Gaudeamus') as result4;

+---------+-----------+---------+---------+
| RESULT1 | RESULT2   | RESULT3 | RESULT4 |
+---------+-----------+---------+---------+
| Gdeamus | GAUdeamus | Gdeamus | NULL    |
+---------+-----------+---------+---------+