- 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 theREPLACE
function simply deletes all occurrences of thepattern
.
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 ofpattern
removed.If
replacement
is specified,subject
is returned with all occurrences ofpattern
replaced byreplacement
.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 | +----------+---------+-------------+------------+