--将字符转化为整数--drop function char_to_int
create function char_to_int(@in_char varchar(1)) returns intasbegin if ISNUMERIC(@in_char)=1 --是 begin declare @out_int int select @out_int=convert(int,@in_char) end else begin --否 select @out_int=-1 end return @out_intend
--15位身份证转化为18位
--drop function sfzh_to_18
create function sfzh_to_18(@befor varchar(2), --前缀18，19，20 @sfzh varchar(30) --15位身份证 ) returns varchar(30)asbegin declare @new_sfzh char(30) declare @old_sfzh varchar(30) select @sfzh=ltrim(rtrim(@sfzh)) select @old_sfzh=substring(@sfzh,1,6)+@befor+substring(@sfzh,7,9) --print @old_sfzh if len(@sfzh)=15 --长度15位 begin declare @check char(1), @char1 char(1), @char2 char(1), @char3 char(1), @char4 char(1), @char5 char(1), @char6 char(1), @char7 char(1), @char8 char(1), @char9 char(1), @char10 char(1), @char11 char(1), @char12 char(1), @char13 char(1), @char14 char(1), @char15 char(1), @char16 char(1), @char17 char(1), @int1 int, @int2 int, @int3 int, @int4 int, @int5 int, @int6 int, @int7 int, @int8 int, @int9 int, @int10 int, @int11 int, @int12 int, @int13 int, @int14 int, @int15 int, @int16 int, @int17 int, @ma int select @char1=substring(@old_sfzh,17,1) select @char2=substring(@old_sfzh,16,1) select @char3=substring(@old_sfzh,15,1) select @char4=substring(@old_sfzh,14,1) select @char5=substring(@old_sfzh,13,1) select @char6=substring(@old_sfzh,12,1) select @char7=substring(@old_sfzh,11,1) select @char8=substring(@old_sfzh,10,1) select @char9=substring(@old_sfzh,9,1) select @char10=substring(@old_sfzh,8,1) select @char11=substring(@old_sfzh,7,1) select @char12=substring(@old_sfzh,6,1) select @char13=substring(@old_sfzh,5,1) select @char14=substring(@old_sfzh,4,1) select @char15=substring(@old_sfzh,3,1) select @char16=substring(@old_sfzh,2,1) select @char17=substring(@old_sfzh,1,1)
select @int1=dbo.char_to_int(@char1) select @int2=dbo.char_to_int(@char2) select @int3=dbo.char_to_int(@char3) select @int4=dbo.char_to_int(@char4) select @int5=dbo.char_to_int(@char5) select @int6=dbo.char_to_int(@char6) select @int7=dbo.char_to_int(@char7) select @int8=dbo.char_to_int(@char8) select @int9=dbo.char_to_int(@char9) select @int10=dbo.char_to_int(@char10) select @int11=dbo.char_to_int(@char11) select @int12=dbo.char_to_int(@char12) select @int13=dbo.char_to_int(@char13) select @int14=dbo.char_to_int(@char14) select @int15=dbo.char_to_int(@char15) select @int16=dbo.char_to_int(@char16) select @int17=dbo.char_to_int(@char17)
select @ma=@int1*(power(2,1) )
+@int2*(power(2,2) ) +@int3*(power(2,3) ) +@int4*(power(2,4) ) +@int5*(power(2,5) ) +@int6*(power(2,6) ) +@int7*(power(2,7) ) +@int8*(power(2,8) ) +@int9*(power(2,9) ) +@int10*(power(2,10) ) +@int11*(power(2,11) ) +@int12*(power(2,12) ) +@int13*(power(2,13) ) +@int14*(power(2,14) ) +@int15*(power(2,15) ) +@int16*(power(2,16) ) +@int17*(power(2,17) ) select @ma=12-(@ma )
select @check=convert(char(1),@ma) if @ma=12 begin select @check='1' end else if @ma=11 begin select @check='0' end else if @ma=10 begin select @check='X' end select @new_sfzh=@old_sfzh+@check end else begin --长度不是15位 select @new_sfzh=@sfzh end
return @new_sfzhend
--18位身份证转化为15位--drop function sfzh_to_15create function sfzh_to_15(@sfzh varchar(30) --18位身份证 ) returns varchar(30)asbegin declare @new_sfzh char(30) declare @old_sfzh varchar(30) select @sfzh=ltrim(rtrim(@sfzh)) if len(@sfzh)=18 --长度18位 begin select @new_sfzh=substring(@sfzh,1,6)+substring(@sfzh,9,9) end else begin --长度不是18位 select @new_sfzh=@sfzh end
return @new_sfzh
end
--测试print dbo.sfzh_to_18('19','310109670404403')
print dbo.sfzh_to_15('31010919670404403X')
