最近在做一个项目时需要在存储过程中判断身份证号的参数的合法性,因此参照网上对应的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