sql实现15位身份证转化为18位身份证

create function f_CID15to18 (@sfz char(18)) 
returns char(18)
as
begin 
 
    declare @osfz varchar(18)
    declare @i int,@ai int,@wi int,@sum int,@mod int,@result int
     
     
    set @osfz = @sfz
    set @sum = 0
    IF len(@osfz) = 15 
    begin
         
        set @osfz = substring(@osfz,1,6) + '19' + substring(@osfz,7,9)
        set @i = 2
         
        while @i <= 18  
        begin
            set @ai = cast(substring(@osfz,19 - @i,1) as int)
            set @wi = POWER (2, (@i - 1))% 11 
            set @sum = @sum + @ai * @wi
            set @i = @I + 1
        end  
 
        set @mod =  @sum % 11 
        set @result = 12 - @mod
 
        IF @result >= 10  
            IF @result = 10  

		   begin 
          
                RETURN @osfz + 'X'
            end 

            ELSE 
            begin 
                set @result = @result - 11
                RETURN @osfz + ltrim(@result)
            end 
        ELSE  
            RETURN @osfz + ltrim(@result)
    end
    ELSE  
        RETURN @sfz
    return @sfz
end 
go

select  dbo.f_CID15to18(tt.SFZH) from  (
	    SELECT TOP 100 SFZH
 FROM [cddpf].[dbo].[T_CJ_RKJCXX] 
WHERE (PERSON_ID >
          (SELECT MAX(PERSON_ID)
         FROM (SELECT TOP 4900 PERSON_ID
                 FROM [cddpf].[dbo].[T_CJ_RKJCXX] 
				  where len(SFZH)=15
                 ORDER BY PERSON_ID) AS T))
				  and   len(SFZH)=15
ORDER BY PERSON_ID
 
	) as tt



drop function f_CID15to18