Categories:

String & Binary Functions (Matching/Comparison)

REPLACE

Removes all occurrences of a specified substring, and optionally replaces them with another string.

Syntax

REPLACE( <subject> , <pattern> [ , <replacement> ] )

Arguments

subject

The subject is the string in which to do the replacements. Typically, this is a column, but it can be a literal.

pattern

This is the substring that you want to replace. Typically, this is a literal, but it can be a column or expression. Note that this is not a “regular expression”; if you want to use regular expressions to search for a pattern, use the REGEXP_REPLACE function.

replacement

This is the value used as a replacement for the pattern. If this is omitted, or is an empty string, then the REPLACE function simply deletes all occurrences of the pattern.

Returns

The returned value is the string after all replacements have been done.

Usage Notes

  • If replacement is not specified, subject is returned with all occurrences of pattern removed.

  • If replacement is specified, subject is returns with all occurrences of pattern replaced by replacement.

  • If any of the arguments is a NULL, the result is also a NULL.

Note

Only occurrences in the original subject are considered. A pattern that occurs in the result is not removed/replaced.

Collation Details

Arguments with collation specifications are currently not supported.

Examples

Replace bc string in abcd with an empty string:

SELECT REPLACE('abcd', 'bc') FROM DUAL;

+-----------------------+
| REPLACE('ABCD', 'BC') |
|-----------------------|
| ad                    |
+-----------------------+

Replace strings in a value with a specified replacement:

CREATE OR REPLACE TABLE replace_example(subject varchar(10), pattern varchar(10), replacement varchar(10));
INSERT INTO replace_example VALUES('snowman', 'snow', 'fire'), ('sad face', 'sad', 'happy');

SELECT subject, pattern, replacement, REPLACE(subject, pattern, replacement) AS new FROM replace_example;

+----------+---------+-------------+------------+
| SUBJECT  | PATTERN | REPLACEMENT | NEW        |
|----------+---------+-------------+------------|
| snowman  | snow    | fire        | fireman    |
| sad face | sad     | happy       | happy face |
+----------+---------+-------------+------------+