SQL函数:15位号码升级为18位

作者: 傻猫    分类: 搞软件     发布时间: 01-21 10:32    浏览次数: 3588     5 条留言

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

Tags: none

添加新评论