我在Hibernate中从MySQL表中检索一些密码值,并将其替换为MySQL中的其他字符串。我知道在Oracle中有translate()函数可以进行替换,但我没有找到相同的MySQL函数。除了MySQL中的Replace()之外,是否还有其他替代方案或可以用于相同目的的库?
我在Hibernate中从MySQL表中检索一些密码值,并将其替换为MySQL中的其他字符串。我知道在Oracle中有translate()函数可以进行替换,但我没有找到相同的MySQL函数。除了MySQL中的Replace()之外,是否还有其他替代方案或可以用于相同目的的库?
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
然后使用:
mysql> select translate('(123) 1234-1234', '( )-','.,.,');
+---------------------------------------------+
| translate('(123) 1234-1234', '( )-','.,.,') |
+---------------------------------------------+
| .123.,1234,1234 |
+---------------------------------------------+
1 row in set (0.00 sec)
subject
,但是迭代器 c
却被用在了 what
和 replace
中? - crsxlSELECT TRANSLATE('Vikas#Bharti-Infy', '#-', '_.') FROM dual;
等效的 MySQL查询 将是 -Vikas_Bharti.Infy
SELECT REPLACE(REPLACE('Vikas#Bharti-Infy', '#', '_'),'-','.');
Vikas_Bharti.Infy
我稍微调整了Flavio的答案,下面这个函数对我来说似乎有效。
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
使用以下示例进行测试:
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)
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');