欢迎来到.net学习网

欢迎联系站长一起更新本网站!QQ:879621940

您当前所在位置:首页 » Sql随手笔记 » 正文

热门阅读

在sql server中使用存储过程验证身份证号码是否合法

创建时间:2012年04月21日 11:53  阅读次数:(10065)
分享到:
在一些人员信息收录的系统中,可能会要求填入人员的身份证号码,但要怎么来验证填入的身份证号码的格式是否合法呢。我们将验证的sql写成一个存储过程,以方便重复调用。

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

下面是该存储过程的所有sql
-- =============================================
-- Author:<Author,,Name >
-- Create date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
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

创建完毕后,我们可以使用以下sql调用来测试是否正确:
declare @result bit
exec @result=dbo.udf_IsvalidIDCard '身份证号码'
select @result

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

简单讲解一下以上用到的系统函数意思:
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函数:将一种数据类型的表达式显式转换为另一种数据类型的表达式。
来源:.net学习网
说明:所有来源为 .net学习网的文章均为原创,如有转载,请在转载处标注本页地址,谢谢!
【编辑:Wyf

打赏

取消

感谢您的支持,我会做的更好!

扫码支持
扫码打赏,您说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

最新评论

共有评论1条
  • #1楼  评论人:匿名  评论时间:2016-9-6 15:40:17
  • 12
发表评论:
留言人:
内  容:
请输入问题 68+25=? 的结果(结果是:93)
结  果: