SQLSERVER校验身份证号 合法性
来源:互联网 发布:编辑矢量图的软件 编辑:程序博客网 时间:2024/04/30 03:49
最近在做一个项目时需要在存储过程中判断身份证号的参数的合法性,因此参照网上对应的C++代码改造成SQL SERVER的函数
C++代码的链接:http://blog.chinaunix.net/uid-27105712-id-4233792.html
CREATE TABLE [dbo].[days] (
[nmonth] int NULL ,
[daynum] int NULL ,
[leap] int NULL
)
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'1', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'2', N'28', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'3', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'4', N'30', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'5', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'6', N'30', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'7', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'8', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'9', N'30', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'10', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'11', N'30', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'12', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'1', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'2', N'29', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'3', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'4', N'30', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'5', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'6', N'30', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'7', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'8', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'9', N'30', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'10', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'11', N'30', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'12', N'31', N'1')
CREATE TABLE [dbo].[VI] (
[num] int NOT NULL IDENTITY(1,1) ,
[alphet] char(1) NULL
)
SET IDENTITY_INSERT [dbo].[VI] ON
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'1', N'1')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'2', N'0')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'3', N'X')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'4', N'9')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'5', N'8')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'6', N'7')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'7', N'6')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'8', N'5')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'9', N'4')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'10', N'3')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'11', N'2')
SET IDENTITY_INSERT [dbo].[VI] OFF
CREATE TABLE [dbo].[WI] (
[num] int NOT NULL IDENTITY(1,1) ,
[w] int NULL
)
SET IDENTITY_INSERT [dbo].[WI] ON
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'1', N'7')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'2', N'9')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'3', N'10')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'4', N'5')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'5', N'8')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'6', N'4')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'7', N'2')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'8', N'1')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'9', N'6')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'10', N'3')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'11', N'7')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'12', N'9')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'13', N'10')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'14', N'5')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'15', N'8')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'16', N'4')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'17', N'2')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'18', N'1')
SET IDENTITY_INSERT [dbo].[WI] OFF
create function checkdate
(
@dt varchar(8)
)
returns int
as
BEGIN
if(substring(@dt, 1, 4) < 1900)
BEGIN
--print '年份超出范围'
return 0
END
declare @month int
set @month = 0
select @month = convert(int, substring(@dt, 5, 2))
if(@month < 0 or @month > 12)
BEGIN
--print '月份超出范围'
return 0
END
declare @leap int
set @leap = 0
declare @year int
set @year = convert(int,substring(@dt, 1, 4))
set @month = convert(int,substring(@dt, 5, 2))
declare @day int
set @day = convert(int, substring(@dt, 7, 2))
declare @day1 int
set @day1 = -1
if(((@year % 4 = 0) and (@year % 100 <> 0)) or (@year % 400 = 0))
BEGIN
set @leap = 1;
END
select @day1 = daynum from days where leap = @leap and nmonth = @month
if(@day > @day1 or @day < 0)
BEGIN
return 0
END
ELSE
BEGIN
return 1
END
return 0
END
create function CHECKSUM
(
@idcard varchar(18)
)
returns char
AS
BEGIN
declare @rtn char
set @rtn = ''
declare @remaining int
set @remaining = 0
declare @ind int
set @ind = 1
declare @sum int
set @sum = 1;
while(@ind <= 17)
BEGIN
set @sum = @sum + (convert(int, substring(@idcard, @ind, 1))) * (select w from WI where num = @ind)
set @ind = @ind + 1
end
set @remaining = @sum % 11
select @rtn = alphet from VI where num = @remaining
return @rtn
END
create function VerifyCertificateID
(
@idcard varchar(18), --身份证号
@sex int --0:男性 1:女性
)
returns int
as
BEGIN
--空身份证号直接返回
if(len(@idcard) = 0)
BEGIN
--print '空身份证号'
return 0
END
--先将15位身份账号延伸到18位
if(len(@idcard) = 15)
BEGIN
--在年前面不上19前缀
set @idcard = SubString(@idcard,1,6) + '19' + SUBSTRING(@idcard, 7, len(@idcard) - 6);
--重新设置最后一位的校验和
set @idcard = @idcard + dbo.CHECKSUM(@idcard);
END
--如果还没到18位,错误
if(len(@idcard) <> 18)
BEGIN
return 0
END
--1到17位必须是数字
declare @ind int
set @ind = 1
while(@ind <= 17)
BEGIN
if(ascii(substring(@idcard, @ind, 1)) < ascii('0') or ascii(substring(@idcard, @ind, 1)) > ascii('9'))
BEGIN
--print '前17位有非数字'
return 0
END
set @ind = @ind + 1
END
--检查第18位的校验和
declare @sum char
set @sum = ''
select @sum = dbo.CHECKSUM(@idcard)
if(@sum <> substring(@idcard, 18, 1))
BEGIN
--print '第18位校验和验证不符'
return 0
END
--验证行政区号
if(convert(int,SUBSTRING(@idcard, 1, 2)) > 65 or convert(int,SUBSTRING(@idcard, 1, 2)) < 11)
BEGIN
--print '验证行政区不符'
return 0
END
declare @verifydt int
set @verifydt = 0
select @verifydt = dbo.checkdate(SUBSTRING(@idcard, 7, 8));
if(@verifydt = 0)
BEGIN
--print '验证日期不符'
return 0
END
declare @nLastThree int;
set @nLastThree = convert(int, SUBSTRING(@idcard, 15, 3));
if(@sex = 0 and (@nLastThree % 2 = 0))
BEGIN
return 0
END
else if(@sex = 1 and (@nLastThree % 2 = 1))
BEGIN
return 0
END
ELSE
BEGIN
return 1
END
return 0
END
- SQLSERVER校验身份证号 合法性
- js --身份证号合法性校验
- sqlserver 身份证号简单校验,筛选
- 身份证号校验
- 身份证号校验
- 身份证号校验
- 身份证号校验
- 验证身份证号的合法性
- 身份证校验(//身份证号合法性验证 //支持15位和18位身份证号//支持地址编码、出生日期、校验位验证)
- java身份证合法性校验并获取并根据身份证号提取身份证相关信息
- JS 身份证号校验 年龄校验
- 身份证号校验的 js
- Javascript身份证号校验
- Javascript身份证号校验
- 身份证号如何校验
- JS严格校验身份证号
- PHP校验身份证号
- 正则表达式校验身份证号
- C# 递归遍历目录与非递归遍历目录
- [学习记录]iOS的一些数学函数
- Linux下获取当前时间到1970年之前某年份的秒数的C代码实现
- 图文解说:Nginx+tomcat配置集群负载均衡
- Groovy语言下的Grails框架
- SQLSERVER校验身份证号 合法性
- Android Matrix
- TFTP与FTP的区别及Linux下TFTP的安装
- Search Insert Position
- clips 6.30 ubuntu 14.04 linux embedding使用
- 【记录】Menu的学习
- 配置nginx+tomcat
- CSDN-markdown编辑器说明[存档]
- jQuery学习笔记(1)---简单的登录输入控制