SQL code
--===================================================================
--15位号码升级为18位
--===================================================================
CreateFUNCTION[dbo].[ID15TO18](@id15char(15))
/*功能：将身份证的15位号码升级为18位
--
-- --调用函数
-- update
-- 表
-- set
-- 身份证号 = dbo.ID15TO18(身份证号)
-- where
-- LEN(身份证号) = 15
--
--
*/
RETURNSCHAR(18)
AS
BEGIN
DECLARE@ID18CHAR(18)
DECLARE@S1ASINTEGER
DECLARE@S2ASINTEGER
DECLARE@S3ASINTEGER
DECLARE@S4ASINTEGER
DECLARE@S5ASINTEGER
DECLARE@S6ASINTEGER
DECLARE@S7ASINTEGER
DECLARE@S8ASINTEGER
DECLARE@S9ASINTEGER
DECLARE@S10ASINTEGER
DECLARE@S11ASINTEGER
DECLARE@S12ASINTEGER
DECLARE@S13ASINTEGER
DECLARE@S14ASINTEGER
DECLARE@S15ASINTEGER
DECLARE@S16ASINTEGER
DECLARE@S17ASINTEGER
DECLARE@S18ASINTEGER
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)
+CASEWHEN@S18=0THEN'1'
WHEN@S18=1THEN'0'
WHEN@S18=2THEN'X'
WHEN@S18=3THEN'9'
WHEN@S18=4THEN'8'
WHEN@S18=5THEN'7'
WHEN@S18=6THEN'6'
WHEN@S18=7THEN'5'
WHEN@S18=8THEN'4'
WHEN@S18=9THEN'3'
WHEN@S18=10THEN'2'
END
RETURN@ID18
END
