学步园

-- --下面定义好的调用函数
-- update
-- 表
-- set
-- 身份证号= dbo.ID15TO18(身份证号)
-- where

-- LEN(身份证号) = 15

dbo.ID15TO18函数

Create FUNCTION [dbo].[ID15TO18] (@id15 char(15))<br /> RETURNS CHAR(18)<br /> AS<br /> BEGIN<br /> DECLARE @ID18 CHAR(18)<br /> DECLARE @S1 AS INTEGER<br /> DECLARE @S2 AS INTEGER<br /> DECLARE @S3 AS INTEGER<br /> DECLARE @S4 AS INTEGER<br /> DECLARE @S5 AS INTEGER<br /> DECLARE @S6 AS INTEGER<br /> DECLARE @S7 AS INTEGER<br /> DECLARE @S8 AS INTEGER<br /> DECLARE @S9 AS INTEGER<br /> DECLARE @S10 AS INTEGER<br /> DECLARE @S11 AS INTEGER<br /> DECLARE @S12 AS INTEGER<br /> DECLARE @S13 AS INTEGER<br /> DECLARE @S14 AS INTEGER<br /> DECLARE @S15 AS INTEGER<br /> DECLARE @S16 AS INTEGER<br /> DECLARE @S17 AS INTEGER<br /> DECLARE @S18 AS INTEGER<br /> SET @S1=SUBSTRING(@ID15,1,1)<br /> SET @S2=SUBSTRING(@ID15,2,1)<br /> SET @S3=SUBSTRING(@ID15,3,1)<br /> SET @S4=SUBSTRING(@ID15,4,1)<br /> SET @S5=SUBSTRING(@ID15,5,1)<br /> SET @S6=SUBSTRING(@ID15,6,1)<br /> SET @S7=1<br /> SET @S8=9<br /> SET @S9=SUBSTRING(@ID15,7,1)<br /> SET @S10=SUBSTRING(@ID15,8,1)<br /> SET @S11=SUBSTRING(@ID15,9,1)<br /> SET @S12=SUBSTRING(@ID15,10,1)<br /> SET @S13=SUBSTRING(@ID15,11,1)<br /> SET @S14=SUBSTRING(@ID15,12,1)<br /> SET @S15=SUBSTRING(@ID15,13,1)<br /> SET @S16=SUBSTRING(@ID15,14,1)<br /> SET @S17=SUBSTRING(@ID15,15,1)</p> <p>SET @S18=((@S1*7) (@S2*9) (@S3*10) (@S4*5) (@S5*8) (@S6*4) (@S7*2) (@S8*1)<br /> (@S9*6) (@S10*3) (@S11*7) (@S12*9) (@S13*10) (@S14*5) (@S15*8) (@S16<br /> *4) (@S17*2))<br /> SET @ID18=SUBSTRING(@ID15,1,6) '19' SUBSTRING(@ID15,7,9)<br /> CASE WHEN @S18=0 THEN '1'<br /> WHEN @S18=1 THEN '0'<br /> WHEN @S18=2 THEN 'X'<br /> WHEN @S18=3 THEN '9'<br /> WHEN @S18=4 THEN '8'<br /> WHEN @S18=5 THEN '7'<br /> WHEN @S18=6 THEN '6'<br /> WHEN @S18=7 THEN '5'<br /> WHEN @S18=8 THEN '4'<br /> WHEN @S18=9 THEN '3'<br /> WHEN @S18=10 THEN '2'<br /> END<br /> RETURN @ID18<br /> END<br />