Is there a MySQL equivalent for Translate () in Oracle
I am extracting some password values from a MySQL table in Hibernate and replacing them with other strings in MySQL. I understand that Oracle has a translation () to replace it, but I haven't found anything like it in MySQL. Will there be an alternative solution other than Replace () in MySQL or any libraries that can be used for the same thing?
source to share
You can create something 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)
source to share
Until now, there is no equivalent for the Oracle TRANSLATE () function in MySQL. However, you can achieve the results you want using nested REPLACE () functions .
Adding an example -
Oracle Query - SELECT TRANSLATE('Vikas#Bharti-Infy', '#-', '_.') FROM dual;
Vikas_Bharti.Infy
The equivalent MySQL query would be -
SELECT REPLACE(REPLACE('Vikas#Bharti-Infy', '#', '_'),'-','.');
Vikas_Bharti.Infy
source to share
I tweaked flavio's answer a bit, it seems to me that the following function works.
CREATE FUNCTION 'translate' (
tar VARCHAR (255),
ori VARCHAR (255),
rpl VARCHAR (255)
) RETURNS VARCHAR (255) CHARSET utf8mb4 DETERMINISTIC BEGIN
DECLARE i INT UNSIGNED DEFAULT 0;
DECLARE cur_char CHAR (1);
DECLARE ori_idx INT UNSIGNED;
DECLARE result VARCHAR (255);
SET result = '';
WHILE i <= length(tar) DO
SET cur_char = mid(tar, i, 1);
SET ori_idx = INSTR(ori, cur_char);
SET result = concat(
result,
REPLACE(
cur_char,
mid(ori, ori_idx, 1),
mid(rpl, ori_idx, 1)
));
SET i = i + 1;
END WHILE;
RETURN result;
END
Tested with the following example:
mysql> SET @map_src = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @map_des = 'nrgzujmaqbetylxwkdohpfvcisNRGZUJMAQBETYLXWKDOHPFVCIS2014587639';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT translate('https://456.HELLO.world', @map_src, @map_des) as f1;
+-------------------------+
| f1 |
+-------------------------+
| ahhwo://458.AUTTX.vxdtz |
+-------------------------+
1 row in set (0.00 sec)
source to share