身份證15轉18位

 身證份15位轉18位

測試案例用＊顯示部分系統人為修改，

 

 

create function f_CID15to18 (@sfz char(18)) returns char(18)asbegin declare @osfz varchar(18)declare @i int,@ai int,@wi int,@sum int,@mod int,@result intset @osfz = @sfz    set @sum = 0IF len(@osfz) = 15     begin        set @osfz = substring(@osfz,1,6) + '19' + substring(@osfz,7,9)        set @i = 2        while @i <= 18          beginset @ai = cast(substring(@osfz,19 - @i,1) as int)set @wi = POWER (2, (@i - 1))% 11 set @sum = @sum + @ai * @wi            set @i = @I + 1end  set @mod =  @sum % 11 set @result = 12 - @modIF @result >= 10  IF @result = 10  RETURN @osfz + 'X'ELSE             begin set @result = @result - 11RETURN @osfz + ltrim(@result)end ELSE  RETURN @osfz + ltrim(@result)    endELSE  RETURN @sfz    return @sfzend goselect dbo.f_CID15to18('32108519760502***9')/*------------------ 32108519760502***9（所影響的行數為 1 行）*/select dbo.f_CID15to18('321085760502***')/*------------------ 32108519760502***9（所影響的行數為 1 行）*/drop function f_CID15to18