TRANSLATE Function - Oracle to MariaDB Migration

In Oracle, the TRANSLATE(string, from_string, to_string) function allows you to replace all occurrences of each character in from_string to the corresponding character in to_string.

In MariaDB you can use nested REPLACE expressions or an user-defined function.

Sometimes Oracle TRANSLATE can be used to replace a single character:

Oracle:

  -- Replace 'a' to 'b'
  SELECT TRANSLATE('abc', 'a', 'b') FROM dual;
  # bbc

In MariaDB you can just use the REPLACE function:

MariaDB:

  -- Replace 'a' to 'b'
  SELECT REPLACE('abc', 'a', 'b');
  # bbc

Usually Oracle TRANSLATE is used to replace multiple characters:

Oracle:

  -- Replace 'a' to '1', 'd' to '2' and remove 'c'
  SELECT TRANSLATE('a b c d e', 'adc', '12') FROM dual;
  # 1 b  2 e

Note that if the from_string is longer than to_string, extra characters are removed from the string.

Now you cannot use single REPLACE function in MariaDB, and you have to use multiple nested REPLACE expressions for every single character in from_string:

MariaDB:

  -- Replace 'a' to '1', 'd' to '2' and remove 'c'
  SELECT REPLACE(REPLACE(REPLACE('a b c d e', 'a', '1'), 'd', '2'), 'c', '');
  # 1 b  2 e

Note that since from_string is longer than to_string the 'c' character is specified to be removed from the input string.

Sometime the from_string can be quite large and it can be inconvenient to use multiple nested REPLACE expressions, so you can use the following user-defined function to implement TRANSLATE in MariaDB:

MariaDB:

  DELIMITER //
 
  CREATE FUNCTION TRANSLATE(p_str TEXT, p_from TEXT, p_to TEXT)
  RETURNS TEXT
  DETERMINISTIC
  BEGIN
    DECLARE v_str TEXT DEFAULT p_str;
 
    DECLARE v_from_char CHAR(1);
    DECLARE v_to_char CHAR(1);
 
    DECLARE v_from_len INT DEFAULT LENGTH(p_from);
    DECLARE v_to_len INT DEFAULT LENGTH(p_to);
    DECLARE i INT DEFAULT 1;
 
    WHILE i <= v_from_len DO
      -- Get the next char to replace
      SET v_from_char = SUBSTRING(p_from, i, 1);
 
      -- Get the next replacement
      SET v_to_char = CASE WHEN i <= v_to_len THEN SUBSTRING(p_to, i, 1) ELSE '' END;
 
      -- Replace all occurrences of this single character
      SET v_str = REPLACE(v_str, v_from_char, v_to_char);
 
      SET i = i + 1;
    END WHILE; 
 
    RETURN v_str;
  END;
  //
 
  DELIMITER ;

Now you do not need the conversion in your SQL queries and can run them as follows:

MariaDB:

  -- Using UDF defined above
  SELECT TRANSLATE('a b c d e', 'adc', '12');
  # 1 b  2 e

For more information, see Oracle to MariaDB Migration.