我有一人事系统表,其中有两个字段如下:
p_old varchar(18) 一代身份证号(已录有数据space(0)、15位身份证号、18位身份证号)
p_new varchar(18) 二代身份证号(拟转换身份证字段)求一SQL语句,要求将所有p_old中原15位身份证号转换成二代身份证号存入字段p_new中我只有一VB.net的算法,可不知怎么写一sql语句进行自动转换算法如下:
Dim sCode15 As String '输入的15位身份证号
Dim IDCode15to18 As String '输出的18位二代身份证号
Dim i As Short
Dim num As Short
Dim code As String
num = 0
IDCode15to18 = Left(sCode15, 6) & "19" & Right(sCode15, 9)
' 计算校验位
For i = 18 To 2 Step -1
num = num + (2 ^ (i - 1) Mod 11) * CDbl(Mid(IDCode15to18, 19 - i, 1))
Next i
num = num Mod 11
Select Case num
Case 0
code = "1"
Case 1
code = "0"
Case 2
code = "X"
Case Else
code = Trim(Str(12 - num))
End Select
IDCode15to18 = IDCode15to18 & code
p_old varchar(18) 一代身份证号(已录有数据space(0)、15位身份证号、18位身份证号)
p_new varchar(18) 二代身份证号(拟转换身份证字段)求一SQL语句,要求将所有p_old中原15位身份证号转换成二代身份证号存入字段p_new中我只有一VB.net的算法,可不知怎么写一sql语句进行自动转换算法如下:
Dim sCode15 As String '输入的15位身份证号
Dim IDCode15to18 As String '输出的18位二代身份证号
Dim i As Short
Dim num As Short
Dim code As String
num = 0
IDCode15to18 = Left(sCode15, 6) & "19" & Right(sCode15, 9)
' 计算校验位
For i = 18 To 2 Step -1
num = num + (2 ^ (i - 1) Mod 11) * CDbl(Mid(IDCode15to18, 19 - i, 1))
Next i
num = num Mod 11
Select Case num
Case 0
code = "1"
Case 1
code = "0"
Case 2
code = "X"
Case Else
code = Trim(Str(12 - num))
End Select
IDCode15to18 = IDCode15to18 & code
--trydeclare @sCode15 as varchar(15) --输入的15位身份证号
declare @IDCode15to18 as varchar(18) --输出的18位二代身份证号
declare @i as int
declare @num as int
--declare @code as varchar(10)
set @num=0
set @IDCode15to18=Left(@sCode15,6)+'19'+Right(@sCode15, 9)--计算校验位
set @i=18
while @i>=1
begin
select @num=@num+(power(2,(@i-1))%11)*cast(substring(@IDCode15to18,19-@i,1) as int)
select @i=@i-1
end
select @num=@num%11
select @IDCode15to18=@IDCode15to18+case @num
when 0 then '1'
when 1 then '0'
when 2 then 'X'
else rtrim(12-@num) end
select @IDCode15to18
returns varchar(18)
as
begin
declare @IDCode15to18 as varchar(18) --输出的18位二代身份证号
declare @i as int
declare @num as int
--declare @code as varchar(10)
set @num=0
set @IDCode15to18=Left(@sCode15,6)+'19'+Right(@sCode15, 9)--计算校验位
set @i=18
while @i>=1
begin
select @num=@num+(power(2,(@i-1))%11)*cast(substring(@IDCode15to18,19-@i,1) as int)
select @i=@i-1
end
select @num=@num%11
select @IDCode15to18=@IDCode15to18+case @num
when 0 then '1'
when 1 then '0'
when 2 then 'X'
else rtrim(12-@num) end
return @IDCode15to18
end--调用方法
select dbo.F_IdCard15to18('340524800101001')--网上随便找的一个号/*
34052419800101001X
*/