在sql server中使用标量函数验证身份证号码是否合法

原文链接：https://www.cnblogs.com/yzxj/p/5096313.html

 

在一些人员信息收录的系统中，可能会要求填入人员的身份证号码，但要怎么来验证填入的身份证号码的格式是否合法呢。我们将验证的sql写成一个存储过程，以方便重复调用。

首先我们要清楚我国身份号码的组成规则：
1，18位身份证号码的组成:6位地区编码＋8位出生年月日＋3位编号（奇数代表男，偶数代表女）＋1位校验码
2，15位身份证号码的组成:6位地区编码＋6位出生年月日＋3位编号（奇数代表男，偶数代表女）

下面是该存储过程的所有sql：

Create FUNCTION [dbo].[Fu_Ba_YZIdentityCard]
(
@IDCardNo varchar(50)=''
)
RETURNS bit
AS
/*******************************************************************
函数名称：Fu_Ba_YZIdentityCard()
参数:@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

创建完毕后，我们可以使用以下sql调用来测试是否正确：

declare @result bit
exec @result=dbo.Fu_Ba_YZIdentityCard '身份证号码'
select @result

返回1，代表身份证号码正确，
返回0，代表身份证号码错误。

或：

if isnull((select dbo.Fu_Ba_YZIdentityCard ('非法身份证')),0)=0
begin
SELECT '500' AS STATUS,'请输入有效的身份证号！' AS msg
RETURN;
end

简单讲解一下以上用到的系统函数意思：
POWER函数：返回给定表达式的指定幂的值。
比如：select POWER(2,3)，就返回2的3次幂，即8

LOWER函数：将大写字符数据转换成小写后返回字符表达式。
比如：select LOWER('ABC')，返回'abc'

substring函数：返回 $sourceString 的子串，从 $startingLoc 指定的位置开始，长度为 $length 指定的字符数。
比如:select substring('abcd',1,2)，返回'ab'
注意：该函数非常有趣
1，$sourceString参数的索引从1开始计。
2，如果你是从0位置开始取值，也不会错，但只会返回'a'
3，如果你是从-1位置开始取值，也不会错，但返回空，但把$length数据填大一些，又会返回值了。
从上面的测试应该可以得出,如果$startingLoc小于或等于0，那么，substring的参数变成了
string($sourceString ,1,$length-1+$startingLoc )
即select substring('abcd',0,2)==select substring('abcd',1,1),
select substring('abcd',-1,2)==select substring('abcd',1,2-1+(-1))
select substring('abcd',-1,3)==select substring('abcd',1,3-1+(-1))
不确定公式就是这样的，但返回结果和这个是一样的。

isdate函数：确定输入表达式是否为有效日期。

ISNUMERIC函数：确定表达式是否为有效的数值类型。

CONVERT函数：将一种数据类型的表达式显式转换为另一种数据类型的表达式。