TSQL--验证身份证是否有效
/****** Object: UserDefinedFunction [dbo].[udf_IsvalidIDCard] Script Date: 02/27/2014 16:03:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[udf_IsvalidIDCard] ( @IDCardNo VARCHAR(50)='' ) 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 GO
网上摘抄来代码,无法追寻出处,见谅。