SQL自定义函数－－隐藏指定长度字符串

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

环境：SQL2008R2 <br>去银行ATM取款或查询时，姓名及卡号会隐藏一部分。<br>今天想试试在SQL中实现，写了个自定义函数。<br>字符串转星号"*"<br><br>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