sqlserver/mysql 替换部分位置的字符串

记录测试演示数据库关键数据修改替换关键字

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