REPLACE¶

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 returned with all occurrences of pattern replaced by replacement.

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

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 |
+----------+---------+-------------+------------+