REPLACE Function - Oracle to MariaDB Migration

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.