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 “regularexpression”; if you want to use regular expressions to search for apattern, use theREGEXP_REPLACE function.

replacement

This is the value used as a replacement for thepattern. If thisis omitted, or is an empty string, then theREPLACE function simplydeletes all occurrences of thepattern.

Returns¶

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

Usage notes¶

• Ifreplacement is not specified,subject is returned with all occurrences ofpattern removed.

• Ifreplacement 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.

Collation details¶

Thecollation specifications of all input arguments must be compatible.

This function does not support the following collation specifications:

• pi (punctuation-insensitive).

• cs-ai (case-sensitive, accent-insensitive).

Examples¶

Replace the stringdown with the stringup:

SELECTREPLACE('down','down','up');

Copy

+-------------------------------+| REPLACE('DOWN', 'DOWN', 'UP') ||-------------------------------|| up                            |+-------------------------------+

Replace the substringAthens in the stringVacationinAthens with the substringRome:

SELECTREPLACE('Vacation in Athens','Athens','Rome');

Copy

+-------------------------------------------------+| REPLACE('VACATION IN ATHENS', 'ATHENS', 'ROME') ||-------------------------------------------------|| Vacation in Rome                                |+-------------------------------------------------+

Replace the substringbc in the stringabcd with an empty substring:

SELECTREPLACE('abcd','bc');

Copy

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

Replace the values in a table with new values.

Create and populate a table:

CREATEORREPLACETABLEreplace_example(subjectVARCHAR(10),patternVARCHAR(10),replacementVARCHAR(10));INSERTINTOreplace_exampleVALUES('old car','old car','new car'),('sad face','sad','happy'),('snowman','snow','fire');

Copy

Replace strings in a value with a specified replacement:

SELECTsubject,pattern,replacement,REPLACE(subject,pattern,replacement)ASnewFROMreplace_exampleORDERBYsubject;

Copy

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

The output shows the following replacements:

• The stringoldcar was replaced by the stringnewcar.

• In the stringsadface, the substringsad was replaced by the substringhappy to create the new stringhappyface.

• In the stringsnowman, the substringsnow was replaced by the substringfire to create the new stringfireman.