不多说 直接代码
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