身份证、组织机构代码及营业执照号校验函数【来自CSDN论坛】

 

原文链接：http://topic.csdn.net/u/20120929/11/C9827091-E067-41DF-BD2B-AE374FD33100.html

发帖人：auro20

SQL code

  
  

   
   

   
   GO
   
   

   
   /*
   
   ***** Object:  UserDefinedFunction [ro].[wj_ValidateIDC]    Script Date: 09/29/2012 11:23:34 *****
   
   */
   
   

   
   SET
   
    ANSI_NULLS 
   
   ON
   
   

   
   GO
   
   

   
   SET
   
    QUOTED_IDENTIFIER 
   
   ON
   
   

   
   GO
   
   

   
   --
   
   验证身份证号码
   
   
--
   
   编者：
   
   
--
   
   创建日期：2012-09-18
   
   
--
   
   参数：@idc 身份证号
   
   

   
   create
   
    
   
   FUNCTION
   
    
   
   [
   
   ro
   
   ]
   
   .
   
   [
   
   wj_ValidateIDC
   
   ]
   
   
(
 
   
   @idc
   
    
   
   varchar
   
   (
   
   18
   
   )
)
   
   RETURNS
   
    
   
   BIT
   
   

   
   AS
   
   

   
   BEGIN
   
   

 
   
   DECLARE
   
    
   
   @validFactors
   
    
   
   VARCHAR
   
   (
   
   17
   
   ),
   
   @validCodes
   
    
   
   VARCHAR
   
   (
   
   11
   
   ),
   
   @i
   
    
   
   TINYINT
   
   ,
   
   @iTemp
   
    
   
   INT
   
   
 
   
   SELECT
   
    
   
   @validFactors
   
   =
   
   '
   
   79A584216379A5842
   
   '
   
   ,
   
   @validCodes
   
   =
   
   '
   
   10X98765432
   
   '
   
   ,
   
   @i
   
   =
   
   1
   
   ,
   
   @iTemp
   
   =
   
   0
   
   
 
   
   --
   
   验证校验位
   
   

   
    
   
   --
   
   根据担保数据库增加条件，否则报错，by WJ
   
   

   
    
   
   IF
   
    (
   
   @idc
   
    
   
   LIKE
   
    
   
   '
   
   x%
   
   '
   
    
   
   OR
   
    
   
   @idc
   
    
   
   LIKE
   
    
   
   '
   
   *%
   
   '
   
    
   
   OR
   
    
   
   @idc
   
    
   
   LIKE
   
    
   
   '
   
   T%
   
   '
   
   )
    
   
   RETURN
   
   (
   
   0
   
   ) 
 
   
   IF
   
    
   
   LEN
   
   (
   
   @idc
   
   )
   
   <>
   
   15
   
    
   
   AND
   
    
   
   LEN
   
   (
   
   @idc
   
   )
   
   <>
   
   18
   
   --
   
   身份证号只有15或18位
   
   

   
     
   
   RETURN
   
   (
   
   0
   
   )
 
   
   IF
   
    
   
   LEN
   
   (
   
   @idc
   
   )
   
   =
   
   15
   
     
   
   --
   
   如果是15位身份证 则只验证日期
   
   

   
        
   
   IF
   
    (
   
   ISDATE
   
   (
   
   '
   
   19
   
   '
   
   +
   
   SUBSTRING
   
   (
   
   @idc
   
   ,
   
   7
   
   ,
   
   6
   
   ))
   
   =
   
   0
   
    
   
   and
   
    
   
   '
   
   19
   
   '
   
   +
   
   SUBSTRING
   
   (
   
   @idc
   
   ,
   
   7
   
   ,
   
   6
   
   ) 
   
   between
   
    
   
   '
   
   1900-01-01
   
   '
   
    
   
   and
   
    
   
   '
   
   2010-01-01
   
   '
   
   )
        
   
   RETURN
   
   (
   
   0
   
   )
     
   
   ELSE
   
   
        
   
   RETURN
   
   (
   
   1
   
   )
 
   
   --
   
   18位身份证 验证日期 校验位
   
   

   
    
   
   IF
   
    (
   
   ISDATE
   
   (
   
   SUBSTRING
   
   (
   
   @idc
   
   ,
   
   7
   
   ,
   
   8
   
   ))
   
   =
   
   0
   
    
   
   and
   
    
   
   SUBSTRING
   
   (
   
   @idc
   
   ,
   
   7
   
   ,
   
   6
   
   ) 
   
   between
   
    
   
   '
   
   1900-01-01
   
   '
   
    
   
   and
   
    
   
   '
   
   2010-01-01
   
   '
   
   )
   
   --
   
   验证日期
   
   

   
       
   
   RETURN
   
   (
   
   0
   
   )
 
   
   --
   
   -验证校验位开始
   
   

   
    
 
   
   WHILE
   
    
   
   @i
   
   <
   
   18
   
   
    
   
   BEGIN
   
   
       
   
   SELECT
   
    
   
   @iTemp
   
   =
   
   @iTemp
   
   +
   
   CAST
   
   (
   
   SUBSTRING
   
   (
   
   @idc
   
   ,
   
   @i
   
   ,
   
   1
   
   ) 
   
   AS
   
    
   
   INT
   
   )
   
   *
   
   
             (
   
   CASE
   
    
   
   SUBSTRING
   
   (
   
   @validFactors
   
   ,
   
   @i
   
   ,
   
   1
   
   ) 
   
   WHEN
   
    
   
   '
   
   A
   
   '
   
    
   
   THEN
   
    
   
   10
   
    
   
   ELSE
   
    
   
   SUBSTRING
   
   (
   
   @validFactors
   
   ,
   
   @i
   
   ,
   
   1
   
   ) 
   
   END
   
   )
             ,
   
   @i
   
   =
   
   @i
   
   +
   
   1
   
   
    
   
   END
   
   
 
   
   IF
   
    
   
   SUBSTRING
   
   (
   
   @validCodes
   
   ,
   
   @iTemp
   
   %
   
   11
   
   +
   
   1
   
   ,
   
   1
   
   )
   
   =RIGHT
   
   (
   
   @idc
   
   ,
   
   1
   
   )
    
   
   RETURN
   
    
   
   1
   
   
 
   
   ELSE
   
   
    
   
   RETURN
   
    
   
   0
   
   
 
   
   RETURN
   
    
   
   0
   
   

   
   END
   
   



   
   GO
   
   

   
   /*
   
   ***** Object:  UserDefinedFunction [ro].[wj_ValidateYYZZNo]    Script Date: 09/29/2012 11:25:49 *****
   
   */
   
   

   
   SET
   
    ANSI_NULLS 
   
   ON
   
   

   
   GO
   
   

   
   SET
   
    QUOTED_IDENTIFIER 
   
   ON
   
   

   
   GO
   
   

   
   --
   
   功能：验证营业执照号
   
   
--
   
   编者：
   
   
--
   
   创建日期：2012-09-20
   
   
--
   
   参数：@ZzNo 营业执照号
   
   

   
   create
   
    
   
   FUNCTION
   
    
   
   [
   
   ro
   
   ]
   
   .
   
   [
   
   wj_ValidateYYZZNo
   
   ]
   
   
(
 
   
   @ZzNo
   
    
   
   varchar
   
   (
   
   15
   
   )
)
   
   RETURNS
   
    
   
   BIT
   
   

   
   AS
   
   

   
   BEGIN
   
   

 
   
   DECLARE
   
    
   
   @m
   
    
   
   INT
   
   , 
   
   @n
   
    
   
   INT
   
   , 
   
   @s
   
    
   
   INT
   
    , 
   
   @p
   
    
   
   INT
   
    ,
   
   @i
   
    
   
   INT
   
    ,
   
   @result
   
    
   
   INT
   
    
 
   
   SELECT
   
    
   
   @m
   
   =
   
   10
   
   ,
   
   @n
   
   =
   
   11
   
   ,
   
   @p
   
   =
   
   @m
   
   
 
 
   
   IF
   
    (
   
   LEN
   
   (
   
   LTRIM
   
   (
   
   RTRIM
   
   (
   
   @ZzNo
   
   )))
   
   =
   
   13
   
    
   
   AND
   
    
   
   ISNUMERIC
   
   (
   
   @ZzNo
   
   ) 
   
   =
   
   1
   
   ) 
 
   
   begin
   
   
 
   
   RETURN
   
    
   
   1
   
   
 
   
   END
   
    
 
   
   IF
   
    (
   
   ISNUMERIC
   
   (
   
   LTRIM
   
   (
   
   RTRIM
   
   (
   
   @ZzNo
   
   )))
   
   =
   
   0
   
   ) 
 
   
   begin
   
   
 
   
   RETURN
   
    
   
   0
   
   
 
   
   end
   
   
 
   
   IF
   
    (
   
   LEN
   
   (
   
   LTRIM
   
   (
   
   RTRIM
   
   (
   
   @ZzNo
   
   )))
   
   <>
   
   15
   
   ) 
 
   
   begin
   
   
 
   
   RETURN
   
    
   
   0
   
   
 
   
   END
   
   
 
 
   
   SET
   
    
   
   @i
   
   =
   
   1
   
   
 
   
   WHILE
   
    
   
   @i
   
    
   
   <=
   
   14
   
   
 
   
   BEGIN
   
    
 
   
   SET
   
    
   
   @s
   
    
   
   =
   
    
   
   SUBSTRING
   
   (
   
   @ZzNo
   
   ,
   
   @i
   
   ,
   
   1
   
   )
 
   
   SET
   
    
   
   @p
   
    
   
   =
   
    
   
   @p
   
   +
   
   @s
   
   
 
   
   SET
   
    
   
   @p
   
    
   
   =
   
   @p
   
   %
   
   @m
   
   
 
   
   IF
   
    
   
   @p
   
    
   
   =
   
    
   
   0
   
   
 
   
   BEGIN
   
   
    
   
   SET
   
    
   
   @p
   
   =
   
    
   
   @m
   
   
 
   
   END
   
    
 
   
   SET
   
    
   
   @p
   
    
   
   =
   
    
   
   @p
   
    
   
   *
   
   2
   
    
 
   
   SET
   
    
   
   @p
   
    
   
   =
   
    
   
   @p
   
   %
   
   @n
   
   
 
   
   SET
   
    
   
   @i
   
   =
   
   @i
   
   +
   
   1
   
   
 
   
   END
   
    
 
   
   SET
   
    
   
   @p
   
    
   
   =
   
    
   
   @p
   
   +
   
   SUBSTRING
   
   (
   
   @ZzNo
   
   ,
   
   15
   
   ,
   
   1
   
   )
 
   
   SET
   
    
   
   @p
   
    
   
   =
   
    
   
   @p
   
   %
   
   @m
   
   
 
 
   
   IF
   
    
   
   @p
   
   =
   
   1
   
    
    
   
   BEGIN
   
   
    
   
   SET
   
    
   
   @result
   
    
   
   =
   
   1
   
   
    
   
   END
   
    
    
   
   ELSE
   
    
    
   
   BEGIN
   
   
    
   
   SET
   
    
   
   @result
   
    
   
   =
   
   0
   
   
    
   
   end
   
   

   
   RETURN
   
    
   
   @result
   
   

   
   END
   
   


   
   GO
   
   

   
   /*
   
   ***** Object:  UserDefinedFunction [ro].[wj_ValidateZZJGNo]    Script Date: 09/29/2012 11:26:32 *****
   
   */
   
   

   
   SET
   
    ANSI_NULLS 
   
   ON
   
   

   
   GO
   
   

   
   SET
   
    QUOTED_IDENTIFIER 
   
   ON
   
   

   
   GO
   
   

   
   --
   
   功能：组织机构号验证
   
   
--
   
   编者：
   
   
--
   
   创建日期：2012-09-20
   
   
--
   
   参数：@ZZJGNo 组织机构号
   
   

   
   ALTER
   
    
   
   FUNCTION
   
    
   
   [
   
   ro
   
   ]
   
   .
   
   [
   
   wj_ValidateZZJGNo
   
   ]
   
   
(
 
   
   @ZZJGNo
   
    
   
   varchar
   
   (
   
   20
   
   )
)
   
   RETURNS
   
    
   
   BIT
   
   

   
   AS
   
   

   
   BEGIN
   
   

   
   DECLARE
   
    
   
   @c
   
    
   
   varchar
   
   (
   
   20
   
   ),
   
   @zz
   
    
   
   int
   
   ,
   
   @z
   
    
   
   int
   
   ,
   
   @i
   
    
   
   INT
   
   ,
   
   @jaz
   
    
   
   VARCHAR
   
   (
   
   30
   
   ),
   
   @C9
   
    
   
   VARCHAR
   
   (
   
   10
   
   )

   
   SET
   
    
   
   @ZZJGNo
   
    
   
   =
   
    
   
   UPPER
   
   (
   
   @ZZJGNo
   
   )


   
   SET
   
    
   
   @i
   
   =
   
   1
   
   

   
   WHILE
   
    
   
   @i
   
    
   
   <=
   
   8
   
   

   
   BEGIN
   
   

   
   SET
   
    
   
   @c
   
   =
   
    
   
   SUBSTRING
   
   (
   
   @ZZJGNo
   
   ,
   
   @i
   
   ,
   
   1
   
   )

   
   IF
   
    
   
   @c
   
   >=
   
   '
   
   A
   
   '
   
    
   
   AND
   
    
   
   @c
   
   <=
   
   '
   
   Z
   
   '
   
   

   
   BEGIN
   
   
  
   
   SET
   
    
   
   @z
   
    
   
   =
   
    (
   
   ASCII
   
   (
   
   @c
   
   )
   
   -
   
   55
   
   ) 
   
   *
   
    (
   
   CASE
   
    
   
   @i
   
    
   
   WHEN
   
    
   
   1
   
    
   
   THEN
   
    
   
   3
   
    
   
   WHEN
   
    
   
   2
   
    
   
   THEN
   
    
   
   7
   
    
   
   WHEN
   
    
   
   3
   
    
   
   THEN
   
    
   
   9
   
    
   
   WHEN
   
    
   
   4
   
    
   
   THEN
   
    
   
   10
   
    
   
   WHEN
   
    
   
   5
   
    
   
   THEN
   
    
   
   5
   
    
   
   WHEN
   
    
   
   6
   
    
   
   THEN
   
    
   
   8
   
    
   
   WHEN
   
    
   
   7
   
    
   
   THEN
   
    
   
   4
   
    
   
   WHEN
   
    
   
   8
   
    
   
   THEN
   
    
   
   2
   
    
   
   END
   
   )

   
   END
   
    

   
   ELSE
   
    
   
   IF
   
    
   
   @c
   
   >=
   
   '
   
   0
   
   '
   
    
   
   AND
   
    
   
   @c
   
   <=
   
   '
   
   9
   
   '
   
    

   
   BEGIN
   
   
  
   
   SET
   
    
   
   @z
   
   =
   
   CONVERT
   
   (NUMERIC,
   
   @c
   
   ) 
   
   *
   
    (
   
   CASE
   
    
   
   @i
   
    
   
   WHEN
   
    
   
   1
   
    
   
   THEN
   
    
   
   3
   
    
   
   WHEN
   
    
   
   2
   
    
   
   THEN
   
    
   
   7
   
    
   
   WHEN
   
    
   
   3
   
    
   
   THEN
   
    
   
   9
   
    
   
   WHEN
   
    
   
   4
   
    
   
   THEN
   
    
   
   10
   
    
   
   WHEN
   
    
   
   5
   
    
   
   THEN
   
    
   
   5
   
    
   
   WHEN
   
    
   
   6
   
    
   
   THEN
   
    
   
   8
   
    
   
   WHEN
   
    
   
   7
   
    
   
   THEN
   
    
   
   4
   
    
   
   WHEN
   
    
   
   8
   
    
   
   THEN
   
    
   
   2
   
    
   
   END
   
   )


   
   END
   
   

   
   ELSE
   
   

   
   BEGIN
   
   

   
   RETURN
   
    
   
   0
   
   

   
   END
   
     

   
   SET
   
    
   
   @zz
   
    
   
   =
   
    
   
   ISNULL
   
   (
   
   @zz
   
   ,
   
   0
   
   )
   
   +
   
   @z
   
    

   
   SET
   
    
   
   @i
   
    
   
   =
   
    
   
   @i
   
   +
   
   1
   
   

   
   END
   
   



   
   SET
   
    
   
   @jaz
   
    
   
   =
   
    
   
   11
   
   -
   
   (
   
   @zz
   
   %
   
   11
   
   )

   
   IF
   
    
   
   @jaz
   
   =
   
   10
   
   

   
   BEGIN
   
   

   
   SET
   
    
   
   @C9
   
    
   
   =
   
    
   
   '
   
   X
   
   '
   
   

   
   END
   
    

   
   ELSE
   
    
   
   IF
   
    
   
   @jaz
   
    
   
   =
   
   11
   
   

   
   BEGIN
   
   

   
   SET
   
    
   
   @C9
   
   =
   
   '
   
   0
   
   '
   
       

   
   END
   
   

   
   ELSE
   
    

   
   BEGIN
   
   

   
   SET
   
    
   
   @C9
   
   =
   
   LTRIM
   
   (
   
   RTRIM
   
   (
   
   @jaz
   
   ))

   
   END
   
    



   
   IF
   
    
   
   @ZZJGNO
   
    
   
   =
   
    
   
   SUBSTRING
   
   (
   
   @ZZJGNO
   
   ,
   
   1
   
   ,
   
   8
   
   ) 
   
   +
   
   @C9
   
   

   
   BEGIN
   
   
    
   
   RETURN
   
    
   
   1
   
    

   
   END
   
    

   
   IF
   
    
   
   @ZZJGNO
   
    
   
   =
   
    
   
   SUBSTRING
   
   (
   
   @ZZJGNO
   
   ,
   
   1
   
   ,
   
   8
   
   )
   
   +
   
   '
   
   -
   
   '
   
   +
   
   @C9
   
   

   
   BEGIN
   
   
    
   
   RETURN
   
    
   
   1
   
   

   
   END
   
   

   
   IF
   
    
   
   @ZZJGNO
   
    
   
   =
   
    
   
   SUBSTRING
   
   (
   
   @ZZJGNO
   
   ,
   
   1
   
   ,
   
   8
   
   ) 
   
   +
   
    
   
   '
   
   —
   
   '
   
    
   
   +
   
   @C9
   
   

   
   BEGIN
   
   
    
   
   RETURN
   
    
   
   1
   
   

   
   END
   
   

   
   IF
   
    
   
   @ZZJGNO
   
    
   
   =
   
    
   
   SUBSTRING
   
   (
   
   @ZZJGNO
   
   ,
   
   1
   
   ,
   
   8
   
   ) 
   
   +
   
   '
   
    
   
   '
   
   +
   
   @C9
   
   

   
   BEGIN
   
   
    
   
   RETURN
   
    
   
   1
   
    

   
   END
   
    
 
   
   RETURN
   
    
   
   0
   
   

   
   END