Is there a MySQL equivalent for Translate() in Oracle

Question

I am retrieving some password values from MySQL table in Hibernate and replace that with other strings in MySQL. I understand that there is translate() in Oracle to do the replacement but I haven't found any of the same in MySQL. Would there be any alternate solution other than Replace() in MySQL or any libraries that can be used for the same?

Answer 1

Till now there is no equivalent of Oracle's TRANSLATE() function in MySQL. However, you can achieve the desired results by using nested REPLACE() functions.

Adding an example -
Oracle query -
SELECT TRANSLATE('Vikas#Bharti-Infy', '#-', '_.') FROM dual;

Vikas_Bharti.Infy

The equivalent MySQL query will be -
SELECT REPLACE(REPLACE('Vikas#Bharti-Infy', '#', '_'),'-','.');

Vikas_Bharti.Infy

Answer 2

You could create one like this:

CREATE FUNCTION `translate`(subject varchar(255), what varchar(255), replace_to varchar(255)) RETURNS varchar(255)
begin
 declare c int unsigned default 0;
 declare result varchar(255);

 set result = subject;

 while c <= length(subject) do
  set result = replace(result, mid(what, c, 1), mid(replace_to, c, 1) );
  set c=c+1;   
 end while;

 return result; 
end

Then use:

mysql> select translate('(123) 1234-1234', '( )-','.,.,');
+---------------------------------------------+
| translate('(123) 1234-1234', '( )-','.,.,') |
+---------------------------------------------+
| .123.,1234,1234                             |
+---------------------------------------------+
1 row in set (0.00 sec)