SqlServer2012+ 数据脱敏脚本

脚本：

declare @name sysname, @idCard sysname, 
        @phone sysname, @email sysname, 
        @address sysname, @full sysname, @part sysname

set @name='李小明'
set @idCard='370305198601010611'
set @phone = '13946875234'
set @email = 'znbxyyy@126.com'
set @address = '山东省xx市xx区xxx号x单元xxx'
set @full = 'abcdefg'
set @part = '一段演示文本内容'

print IIF(LEN(@name)>2, CONCAT(LEFT(@name,1), '**' ), CONCAT(LEFT(@name,1), '' ))
print IIF(LEN(@idCard)>6, CONCAT(LEFT(@idCard,4), '************', RIGHT(@idCard,2)),'')
print IIF(LEN(@phone)>0, CONCAT(LEFT(@phone,3), '****' ,RIGHT(@phone,4)),'')
print IIF(LEN(@email)>3,CONCAT(LEFT(@email,3), '**@**' ,RIGHT(@email,3)),'')
print IIF(LEN(@address)>8,CONCAT(LEFT(@address,5), '**********' ,RIGHT(@address,3)),'')
print REPLICATE('*',LEN(@full))
print IIF(LEN(@part)>2, CONCAT(LEFT(@part, 1), REPLICATE('*', LEN(@part)-2), RIGHT(@part, 1)),
      IIF(LEN(@part) > 1, CONCAT(LEFT(@part, 1), REPLICATE('*', LEN(@part) - 1)), 
      IIF(LEN(@part) > 0, '*', '')))

--字符串中间2/3内容脱敏
declare @nDesLen int, @nStart int, @sSign sysname
set @nDesLen= FLOOR(LEN(@part)*2.0/3.0)
set @nStart= FLOOR(LEN(@part)/2.0-@nDesLen/2.0)
print CONCAT(substring(@part,1,@nStart),REPLICATE('*',@nDesLen),
　　　 substring(@part,@nStart+@nDesLen+1,LEN(@part)-@nStart+@nDesLen))

结果：

 SqlServer 2012 版以下，使用 case when 替换 iif