UPDATE 员工表 SET 身份证号= SUBSTRING(身份证号,1,6)+'18'+SUBSTRING(身份证号,7,9)+ SUBSTRING('10X98765432', ( CAST(SUBSTRING(身份证号, 1,1) AS INT)*7 +CAST(SUBSTRING(身份证号, 2,1) AS INT)*9 +CAST(SUBSTRING(身份证号, 3,1) AS INT)*10 +CAST(SUBSTRING(身份证号, 4,1) AS INT)*5 +CAST(SUBSTRING(身份证号, 5,1) AS INT)*8 +CAST(SUBSTRING(身份证号, 6,1) AS INT)*4 +1*2 +8*1 +CAST(SUBSTRING(身份证号, 7,1) AS INT)*6 +CAST(SUBSTRING(身份证号, 8,1) AS INT)*3 +CAST(SUBSTRING(身份证号, 9,1) AS INT)*7 +CAST(SUBSTRING(身份证号,10,1) AS INT)*9 +CAST(SUBSTRING(身份证号,11,1) AS INT)*10 +CAST(SUBSTRING(身份证号,12,1) AS INT)*5 +CAST(SUBSTRING(身份证号,13,1) AS INT)*8 +CAST(SUBSTRING(身份证号,14,1) AS INT)*4 +CAST(SUBSTRING(身份证号,15,1) AS INT)*2 ) % 11 + 1, 1) WHERE LEN(身份证号)=15 AND SUBSTRING(身份证号,13,3) IN ('999','998','997','996')-- 15位身份证号升级为18位,适用于19xx年出生的公民 UPDATE 员工表 SET 身份证号= SUBSTRING(身份证号,1,6)+'19'+SUBSTRING(身份证号,7,9)+ SUBSTRING('10X98765432', ( CAST(SUBSTRING(身份证号, 1,1) AS INT)*7 +CAST(SUBSTRING(身份证号, 2,1) AS INT)*9 +CAST(SUBSTRING(身份证号, 3,1) AS INT)*10 +CAST(SUBSTRING(身份证号, 4,1) AS INT)*5 +CAST(SUBSTRING(身份证号, 5,1) AS INT)*8 +CAST(SUBSTRING(身份证号, 6,1) AS INT)*4 +1*2 +9*1 +CAST(SUBSTRING(身份证号, 7,1) AS INT)*6 +CAST(SUBSTRING(身份证号, 8,1) AS INT)*3 +CAST(SUBSTRING(身份证号, 9,1) AS INT)*7 +CAST(SUBSTRING(身份证号,10,1) AS INT)*9 +CAST(SUBSTRING(身份证号,11,1) AS INT)*10 +CAST(SUBSTRING(身份证号,12,1) AS INT)*5 +CAST(SUBSTRING(身份证号,13,1) AS INT)*8 +CAST(SUBSTRING(身份证号,14,1) AS INT)*4 +CAST(SUBSTRING(身份证号,15,1) AS INT)*2 ) % 11 + 1, 1) WHERE LEN(身份证号)=15 AND SUBSTRING(身份证号,13,3) NOT IN ('999','998','997','996')
create function [dbo].[id15to18] (@id15 char(15)) /*功能:将身份证的15位号码升级为18位 -- --调用函数 -- update -- 表 -- set -- 身份证号= dbo.id15to18(身份证号) -- where -- len(身份证号) = 15 -- -- */ returns char(18) as begin declare @id18 char(18)
declare @s1 as integer declare @s2 as integer declare @s3 as integer declare @s4 as integer declare @s5 as integer declare @s6 as integer declare @s7 as integer declare @s8 as integer declare @s9 as integer declare @s10 as integer declare @s11 as integer declare @s12 as integer declare @s13 as integer declare @s14 as integer declare @s15 as integer declare @s16 as integer declare @s17 as integer declare @s18 as integer
set @s1=substring(@id15,1,1) set @s2=substring(@id15,2,1) set @s3=substring(@id15,3,1) set @s4=substring(@id15,4,1) set @s5=substring(@id15,5,1) set @s6=substring(@id15,6,1) set @s7=1 set @s8=9 set @s9=substring(@id15,7,1) set @s10=substring(@id15,8,1) set @s11=substring(@id15,9,1) set @s12=substring(@id15,10,1) set @s13=substring(@id15,11,1) set @s14=substring(@id15,12,1) set @s15=substring(@id15,13,1) set @s16=substring(@id15,14,1) set @s17=substring(@id15,15,1)
set @s18=((@s1*7)+(@s2*9)+(@s3*10)+(@s4*5)+(@s5*8)+(@s6*4)+(@s7*2)+(@s8*1) +(@s9*6)+(@s10*3)+(@s11*7)+(@s12*9)+(@s13*10)+(@s14*5)+(@s15*8)+(@s16 *4)+(@s17*2))%11
set @id18=substring(@id15,1,6)+'19'+substring(@id15,7,9) +case when @s18=0 then '1' when @s18=1 then '0' when @s18=2 then 'x' when @s18=3 then '9' when @s18=4 then '8' when @s18=5 then '7' when @s18=6 then '6' when @s18=7 then '5' when @s18=8 then '4' when @s18=9 then '3' when @s18=10 then '2' end
return @id18 end select [dbo].[id15to18]('230882850504491')
SUBSTRING(身份证号,1,6)+'18'+SUBSTRING(身份证号,7,9)+
SUBSTRING('10X98765432',
(
CAST(SUBSTRING(身份证号, 1,1) AS INT)*7
+CAST(SUBSTRING(身份证号, 2,1) AS INT)*9
+CAST(SUBSTRING(身份证号, 3,1) AS INT)*10
+CAST(SUBSTRING(身份证号, 4,1) AS INT)*5
+CAST(SUBSTRING(身份证号, 5,1) AS INT)*8
+CAST(SUBSTRING(身份证号, 6,1) AS INT)*4
+1*2
+8*1
+CAST(SUBSTRING(身份证号, 7,1) AS INT)*6
+CAST(SUBSTRING(身份证号, 8,1) AS INT)*3
+CAST(SUBSTRING(身份证号, 9,1) AS INT)*7
+CAST(SUBSTRING(身份证号,10,1) AS INT)*9
+CAST(SUBSTRING(身份证号,11,1) AS INT)*10
+CAST(SUBSTRING(身份证号,12,1) AS INT)*5
+CAST(SUBSTRING(身份证号,13,1) AS INT)*8
+CAST(SUBSTRING(身份证号,14,1) AS INT)*4
+CAST(SUBSTRING(身份证号,15,1) AS INT)*2
)
% 11 + 1, 1)
WHERE LEN(身份证号)=15 AND SUBSTRING(身份证号,13,3) IN ('999','998','997','996')-- 15位身份证号升级为18位,适用于19xx年出生的公民
UPDATE 员工表 SET 身份证号=
SUBSTRING(身份证号,1,6)+'19'+SUBSTRING(身份证号,7,9)+
SUBSTRING('10X98765432',
(
CAST(SUBSTRING(身份证号, 1,1) AS INT)*7
+CAST(SUBSTRING(身份证号, 2,1) AS INT)*9
+CAST(SUBSTRING(身份证号, 3,1) AS INT)*10
+CAST(SUBSTRING(身份证号, 4,1) AS INT)*5
+CAST(SUBSTRING(身份证号, 5,1) AS INT)*8
+CAST(SUBSTRING(身份证号, 6,1) AS INT)*4
+1*2
+9*1
+CAST(SUBSTRING(身份证号, 7,1) AS INT)*6
+CAST(SUBSTRING(身份证号, 8,1) AS INT)*3
+CAST(SUBSTRING(身份证号, 9,1) AS INT)*7
+CAST(SUBSTRING(身份证号,10,1) AS INT)*9
+CAST(SUBSTRING(身份证号,11,1) AS INT)*10
+CAST(SUBSTRING(身份证号,12,1) AS INT)*5
+CAST(SUBSTRING(身份证号,13,1) AS INT)*8
+CAST(SUBSTRING(身份证号,14,1) AS INT)*4
+CAST(SUBSTRING(身份证号,15,1) AS INT)*2
)
% 11 + 1, 1)
WHERE LEN(身份证号)=15 AND SUBSTRING(身份证号,13,3) NOT IN ('999','998','997','996')
http://blog.csdn.net/roy_88/archive/2009/02/17/3898876.aspx
create function [dbo].[id15to18] (@id15 char(15))
/*功能:将身份证的15位号码升级为18位
-- --调用函数
-- update
-- 表
-- set
-- 身份证号= dbo.id15to18(身份证号)
-- where
-- len(身份证号) = 15
--
--
*/
returns char(18)
as
begin
declare @id18 char(18)
declare @s1 as integer
declare @s2 as integer
declare @s3 as integer
declare @s4 as integer
declare @s5 as integer
declare @s6 as integer
declare @s7 as integer
declare @s8 as integer
declare @s9 as integer
declare @s10 as integer
declare @s11 as integer
declare @s12 as integer
declare @s13 as integer
declare @s14 as integer
declare @s15 as integer
declare @s16 as integer
declare @s17 as integer
declare @s18 as integer
set @s1=substring(@id15,1,1)
set @s2=substring(@id15,2,1)
set @s3=substring(@id15,3,1)
set @s4=substring(@id15,4,1)
set @s5=substring(@id15,5,1)
set @s6=substring(@id15,6,1)
set @s7=1
set @s8=9
set @s9=substring(@id15,7,1)
set @s10=substring(@id15,8,1)
set @s11=substring(@id15,9,1)
set @s12=substring(@id15,10,1)
set @s13=substring(@id15,11,1)
set @s14=substring(@id15,12,1)
set @s15=substring(@id15,13,1)
set @s16=substring(@id15,14,1)
set @s17=substring(@id15,15,1)
set @s18=((@s1*7)+(@s2*9)+(@s3*10)+(@s4*5)+(@s5*8)+(@s6*4)+(@s7*2)+(@s8*1)
+(@s9*6)+(@s10*3)+(@s11*7)+(@s12*9)+(@s13*10)+(@s14*5)+(@s15*8)+(@s16
*4)+(@s17*2))%11
set @id18=substring(@id15,1,6)+'19'+substring(@id15,7,9)
+case when @s18=0 then '1'
when @s18=1 then '0'
when @s18=2 then 'x'
when @s18=3 then '9'
when @s18=4 then '8'
when @s18=5 then '7'
when @s18=6 then '6'
when @s18=7 then '5'
when @s18=8 then '4'
when @s18=9 then '3'
when @s18=10 then '2'
end
return @id18
end
select [dbo].[id15to18]('230882850504491')