9

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?

Sergey Maksimenko
  • 587
  • 7
  • 22
John
  • 91
  • 1
  • 1
  • 3
  • try this: http://stackoverflow.com/questions/1755408/mysql-regex-replace – Vao Tsun Jun 26 '15 at 06:20
  • Thanks Vao. But it doesnt work. What I require is like the following if in Oracle: translate(Password,'filqsnmpktyzJIjuv','abcdefghijklm') I wish to know what can be an alternative for this TRANSLATE in MySQL!! – John Jun 26 '15 at 07:52

4 Answers4

5

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

Vikas Bharti
  • 61
  • 1
  • 7
5

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)
Flavio Maia
  • 51
  • 1
  • 2
  • this solution seems buggy, why the main loop is iterating over `subject`, however, the iterator `c` was used in `what` and `replace`? – crsxl Oct 10 '19 at 07:36
0

I tweaked Flavio's answer a little bit, the following function seems to work for me.

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)
crsxl
  • 185
  • 9
0
delimiter //
drop function if exists my_translate_fx //
create function my_translate_fx (p_str varchar(1024), p_ori varchar(1024), p_rep varchar(1024))
    -- Replace characters p_ori with corresponding characters p_rep in string p_str
returns varchar(1024)
begin
  declare l_ori     varchar(1024)   default ifnull(p_ori,'') ;
  declare l_rep     varchar(1024)   default ifnull(p_rep,'') ;
  declare l_len_ori int unsigned    default ifnull(length(p_ori),0) ;
  declare l_len_rep int unsigned    default ifnull(length(p_rep),0) ;
  declare l_res     varchar(1024)   default p_str ;
  declare l_last_rep_chr varchar(6) default '' ;
  declare l_pos_ori int unsigned    default 1;

  -- No change if no original char to replace
  if l_len_ori < 1 then
    return l_res ;
  end if;

  -- Replacement string p_rep to be same size as p_ori, when p_rep is not null
  -- Complete replacement string repeating the last char in p_rep, eg. ' '
  if l_len_rep > 0 then
    set l_last_rep_chr := substr( l_rep ,l_len_rep,1) ;
    while l_len_rep < l_len_ori do
      set l_len_rep := l_len_rep + 1 ;
      set l_rep := concat( l_rep, l_last_rep_chr );
    end while ;
  end if;

  while l_pos_ori <= l_len_ori do
    set l_res = replace(l_res, substr(l_ori, l_pos_ori, 1), substr(l_rep, l_pos_ori, 1) );
    set l_pos_ori = l_pos_ori + 1;
  end while;

  return l_res ;
end;
//
delimiter ;

select my_translate_fx( '123123','23',' ');
-- select my_translate_fx( '123123','23','xy');
-- select my_translate_fx( '123123','1',' x');
-- select my_translate_fx( '123123','23','');
-- select my_translate_fx( '123123','','z');
Denis Berger
  • 1