1. REPLACE() String Function

The REPLACE() function is used to replace all occurrences of a substring within a string.

Syntax

REPLACE(str, from_str, to_str)

• str: The original string.
• from_str: The substring to find.
• to_str: The substring to replace with.

Example

SELECT REPLACE('Hello World', 'World', 'MySQL');
-- Result: 'Hello MySQL'

or

SELECT REPLACE('banana', 'a', 'o');
-- Result: 'bonono'

• All occurrences of 'a' are replaced with 'o'.

2. REPLACE Statement (Table Row Upsert)

MySQL also has a REPLACE INTO statement, which is similar to INSERT, but:

• If a row with the same primary key or unique index exists, it deletes the old row and inserts the new one.
• If no such row exists, it simply inserts.

Syntax

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);