环境:SQL2008R2
去银行ATM取款或查询时,姓名及卡号会隐藏一部分。
今天想试试在SQL中实现,写了个自定义函数。
字符串转星号"*"
if object_id('f_hidenString','fn')>0
drop function f_hidenString;
go
/* f_hidenString():隱藏指定長度字符串, 1) 當字符串長度大於需要隱藏字符長度時 ,隱藏指定長度輸出
** 2) 當字符串長度小於等於需要隱藏字符長度時,只顯示第一個字符,其他都隱藏
** 參數:@sourceString: 源字符串
** @hidenNumber : 隱藏字符串長度
** 返回: 字符串
*/
create function f_hidenString(
@sourceString nvarchar(4000),
@hidenNumber int
)
returns nvarchar(4000)
as
begin
declare @strLength int;
declare @halfLength int;
declare @length int;
declare @startPosition int;
declare @endPosition int;
declare @result nvarchar(4000);
declare @maskString nvarchar(400); -- 掩码
if coalesce(@sourceString,N'~')<>N'~'
set @Length = len(@sourceString)
if coalesce(@hidenNumber,0)>0
begin
-- 1) 當字符串長度大於 需要隱藏字符長度時 ,隱藏指定長度輸出
if @length > @hidenNumber
begin
set @startPosition = (@length /2) - (@hidenNumber/2)
if @startPosition = 0
begin
set @startPosition = 1
end;
set @endPosition = @startPosition + (@hidenNumber);
end;
else if (( @length >= 2 ) and (@length < @hidenNumber ))
-- 2) 當字符串長度大於等於 需要隱藏字符一半長度時
begin
set @hidenNumber = len(@sourceString)/2
set @startPosition = (@length /2) - ceiling(@hidenNumber /2)
if @startPosition = 0
begin
set @startPosition = 1
end;
set @endPosition = @startPosition + (@hidenNumber)
end;
else if (( @length >= 2 ) and (@length = @hidenNumber ))
-- 2) 當字符串長度大於等於 需要隱藏字符一半長度時
begin
set @hidenNumber = @hidenNumber-1
set @startPosition = (@length /2) - (@hidenNumber /2)
if @startPosition = 0
begin
set @startPosition = 1;
set @endPosition = @length-1;
end;
set @endPosition = @startPosition + (@hidenNumber) ;
end;
else
begin
set @startPosition = 1;
set @endPosition = 1;
set @hidenNumber = 0
end;
set @maskString = replicate(N'*', @hidenNumber)
set @result = subString(@sourceString,1,@startPosition) + coalesce(@maskString ,N'')
+ substring(@sourceString,@endPosition+1,@length) ;
end
else
begin
-- 3) 當字符串長度小於等於1 ,原樣輸出
set @result = @sourceString;
end;
return (@result)
end
go
--測試
select dbo.f_hidenString('13698763747',5) as hidenString
union all
select dbo.f_hidenString('13612343747',4) as hidenString
union all
select dbo.f_hidenString(N'東莞市顯示技術有限公司',5) as hidenString
union
select dbo.f_hidenString('1364',3) as hidenString
union all
select dbo.f_hidenString(N'136',3) as hidenString
union all
select dbo.f_hidenString(N'13665',3) as hidenString
union all
select dbo.f_hidenString(N'12',2) as hidenString
union all
select dbo.f_hidenString(N'1',1) as hidenString