使用SQL对身份证合法性检验

不多说   直接代码

 

 

USE [xd]
GO
/****** Object:  UserDefinedFunction [dbo].[IsvalidIDCard]    Script Date: 04/24/2013 16:37:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[IsvalidIDCard](@IDCardNo varchar(18))
RETURNS bit
AS
/*******************************************************************
函数名称：udf_IsvalidIDCard()
参数:@IDCardNo string 身份证号码
返回值：  bit 是否有效
功能描述：判断身份证号码是否合法

备注：目前中国的身份证号码有18位和15位.
1，18位身份证号码的组成:6位地区编码＋8位出生年月日＋3位编号（奇男偶女）＋1位校验码
2，15位身份证号码的组成:6位地区编码＋6位出生年月日＋3位编号（奇男偶女）
*******************************************************************/
BEGIN

declare @Length int,
 @Loop int,
 @Sum int
declare @SingleChar char

set @Sum = 0
if @IDCardNo is null or @IDCardNo = null or ltrim(rtrim(@IDCardNo)) = ''
begin
return 0
end

set @Length = len(@IDCardNo)
--判断位数
if @Length < > 18 and @Length < > 15
 begin
 return 0 
 end
if @Length = 18
 begin
 if isnumeric(left(@IDCardNo, 17)) = 0
  begin 
  return 0
  end
 if isdate(substring(@IDCardNo, 7, 4) + '-' + substring(@IDCardNo, 11, 2) + '-' + substring(@IDCardNo, 13, 2)) = 0
  begin
  return 0
  end
 set @Loop = 17
 while (@Loop  >= 1)
  begin
  set @Sum = @Sum + convert(int,substring(@IDCardNo, @Loop, 1)) * (power(2,(18 - @Loop)) % 11)
  set @Loop = @Loop - 1
  end
 set @Loop = @Sum % 11
 if @Loop = 0
    begin
  set @SingleChar = '1'
  end
   else if @Loop = 1
  begin
  set @SingleChar = '0'
  end
   else if @Loop = 2
  begin
  set @SingleChar = 'X'
  end
   else
  begin
  set @SingleChar = convert(varchar(2),(12 - @Loop))
  end
 if lower(Right(@IDCardNo, 1)) < > lower(@SingleChar)
  begin
  return 0
  end
 end
else if @Length = 15
 begin
 if isnumeric(@IDCardNo) = 0
  begin
  return 0
  end  
 if isdate('19' + substring(@IDCardNo, 7, 2) + '-' + substring(@IDCardNo, 9, 2) + '-' + substring(@IDCardNo, 11, 2)) = 0
  begin
  return 0
  end
 end

return 1

END