In Oracle and MariaDB, the REPLACE function allows you to replace or remove the specified substring from a string.
Oracle REPLACE accepts 2 or 3 parameters, while MariaDB REPLACE always requires 3 parameters even in Oracle Compatibility mode.
Oracle:
-- When 2 parameters are specified the substring is removed SELECT REPLACE('abc', 'b') FROM dual; # ab -- Replacing with empty string '' is another way to remove the substring SELECT REPLACE('abc', 'b', '') FROM dual; # ab -- Third parameter allows you to specify the replacement string SELECT REPLACE('abc', 'b', 'dd') FROM dual; # addc
When converting Oracle REPLACE with 2 parameters, you have to add the third parameter '' (empty string) in MariaDB.
MariaDB - Oracle Compatibility:
-- Removing the substring SELECT REPLACE('abc', 'b', ''); # ab -- Third parameter allows you to specify the replacement string SELECT REPLACE('abc', 'b', 'dd'); # addc
For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.