将查询到数据表中的相应字符串显示成*号
--直接查询
select replace(name,substring(name,2,2),'**') as 姓名,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(address,'0','*'),'1','*'),'2','*'),'3','*'),'4','*'),'5','*'),'6','*'),'7','*'),'8','*'),'9','*') as 地址,
replace(telcode,substring(telcode,2,6),'******') as 电话
from db where address like '%沪松公路195%'
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(address,'0','*'),'1','*'),'2','*'),'3','*'),'4','*'),'5','*'),'6','*'),'7','*'),'8','*'),'9','*') as 地址,
replace(telcode,substring(telcode,2,6),'******') as 电话
from db where address like '%沪松公路195%'
--通过函数将地址的数字改为*号
--建立GET_NUMBER函数
CREATE FUNCTION DBO.GET_NUMBER(@S NVARCHAR(50))
RETURNS NVARCHAR(50)
AS
BEGIN
WHILE PATINDEX('%[0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[0-9]%',@s),1,'*')
END
RETURN @S
END
GO
RETURNS NVARCHAR(50)
AS
BEGIN
WHILE PATINDEX('%[0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[0-9]%',@s),1,'*')
END
RETURN @S
END
GO
--进行查询
SELECT name,dbo.GET_NUMBER([ADDRESS]),telcode FROM db where address like '%沪松公路195%'
SELECT name,dbo.GET_NUMBER([ADDRESS]),telcode FROM db where address like '%沪松公路195%'