记录测试演示数据库关键数据修改替换关键字
sqlserver
SELECT STUFF(idcard, LEN(idcard) -3, 4,'XXXX') from stinfo
update stinfo set idcard = STUFF(idcard, LEN(idcard) -3, 4,'XXXX')
update stinfo set hostelphone=STUFF(hostelphone, LEN(hostelphone) -3, 4,'XXXX')
update stinfo set phoneno=STUFF(phoneno, LEN(phoneno) -3, 4,'XXXX')
update stinfo set jhrsfz1=STUFF(jhrsfz1, LEN(jhrsfz1) -3, 4,'XXXX')
update stinfo set jhrsfz2=STUFF(jhrsfz2, LEN(jhrsfz2) -3, 4,'XXXX')
update stinfo set lxPhone=STUFF(lxPhone, LEN(lxPhone) -3, 4,'XXXX')
update stinfo set jhrmc1=STUFF(jhrmc1, LEN(jhrmc1), 1,'X')
update stinfo set jhrmc2=STUFF(jhrmc2, LEN(jhrmc2), 1,'X')
update stinfo set name=STUFF(name, LEN(name), 1,'X')
update teacher set name = STUFF(name, LEN(name), 1,'X')
mysql
创建一个函数
CREATE DEFINER=`skip-grants user`@`skip-grants host` FUNCTION `stuff`( str VARCHAR ( 8000 ), startIndex INT, length INT, Newstr VARCHAR ( 8000 ) ) RETURNS varchar(8000) CHARSET utf8
DETERMINISTIC
BEGIN
RETURN concat(
LEFT ( str, startIndex - 1 ),
Newstr,
RIGHT ( str, char_length( str ) - LOCATE( SUBSTRING( str, startIndex, length ), str ) - length + 1 )
);
END
使用:
select stuff(realname,CHAR_LENGTH(realname),1,'X') from sys_user;
参考:https://blog.csdn.net/wojiaowudia/article/details/105484606