REPLACE Function - Oracle to MySQL Migration

In Oracle and MySQL, the REPLACE function allows you to replace or remove the specified substring from a string.

Note that Oracle REPLACE accepts 2 or 3 parameters, while MySQL REPLACE requires 3 parameters.

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 MySQL.

MySQL:

  -- 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 MySQL Migration.