回复人：callzjy(快升五星了) ( 三级(初级)) 信誉：100 2006-10-30 16:06:03 得分:33
? /*
参数说明
@vidcard:需要处理的身份证号
@ixb:身份证中需要对性别进行验证的1男0女-1表示不进行性别验证
@dtoday:今天日期,用于计算年龄和验证身份证的在效性返回表字段说明
idcard:原身份证号
xym:校验码1成功,0失败
xx:校验信息
newidcard:新身份证号,用于15位转18位
csny:出生年月
nl_y:虚岁年龄
nl_m:实际年龄,按月
nl_d:实际年龄,按天
xb:性别
csd:出生地
xz:星座
sx:生宵调用方式
declare @d datetime
set @d=getdate()
select * from dbo.fun_id_card('身份证号',-1,@d)
*/
create function fun_id_card(@vidcard varchar(18),@ixb int,@dtoday datetime)
returns @retidcardreports table(idcard varchar(18) primary key,
xym int not null,
xx varchar(100) not null,
newidcard varchar(18) not null,
csny varchar(8) not null,
nl_y int not null,
nl_m float not null,
nl_d float not null,
xb int not null,
xb_c varchar(4) not null,
csd varchar(100) not null,
xz varchar(20) not null,
sx varchar(4) not null
)
as
begin
declare @new_id_card varchar(18)
declare @birthday varchar(8)
declare @nl_y int
declare @nl_m float
declare @nl_d float
declare @xym int
declare @xx varchar(100)
declare @isdate int
declare @num varchar(17)
declare @i int
declare @sum int
declare @verifycode varchar(1)
declare @m int
declare @xb int
declare @xb_c varchar(4)
declare @csd varchar(100)
declare @xz varchar(10)
declare @sx varchar(2)set @xym=1
set @xx='身份证合法'If Len(@vidcard) < 15 Or Len(@vidcard) = 16 Or Len(@vidcard) = 17 Or Len(@vidcard) > 18
begin
set @xym=0
set @xx='身份证只允许有15位号码或18位号码'
endIf Len(@vidcard) = 18 set @num = left(@vidcard, 17)
else If Len(@vidcard) = 15 set @num = Left(@vidcard, 6) + '19' + Right(@vidcard, 9)
else set @num=''
If (IsNumeric(@num)=0) and (@xym=1)
begin
set @xym=0
set @xx='身份证除最后一位外，必须为数字'
Endif @xym=1 select @birthday=case len(@vidcard) when 18 then substring(@vidcard,7,8) when 15 then '19'+substring(@vidcard,7,6) end
else select @birthday= ''
select @isdate=isdate(@birthday)
if @xym=1
begin
if @isdate=0
begin
set @xym=0
set @xx='身份证出生日期无效'
set @birthday= ''
end
else if (DateDiff(yyyy,@dtoday,cast(@birthday as datetime))<-140) or
(DateDiff(dd,@dtoday,cast(@birthday as datetime))>1)
begin
set @xym=0
set @xx='身份证出生日期范围无效'
end
endif @xym=1
begin
select @nl_y=datediff(yyyy, @birthday, @dtoday)+1select @nl_m=case len(@vidcard) when 18 then cast(datediff(mm,substring(@vidcard,7,8),@dtoday) as real)/12
else cast(datediff(mm,substring(@vidcard,7,6),@dtoday) as real)/12 endselect @nl_d=datediff(yyyy, @birthday, @dtoday) +
1.0 * datediff(dd, dateadd(yyyy, datediff(yyyy, @birthday, @dtoday), @birthday),@dtoday) /
case when (year(@dtoday) % 4)+(year(@dtoday) % 400) = 0 then 366 else 365 end
end
else select @nl_y=0,@nl_m=0,@nl_d=0if @xym=1
begin
set @m=cast(right(@num,3) as int) % 2
select @xb=case @m when 0 then 0 else 1 end,@xb_c=case @m when 0 then '女' else '男' end
if (@ixb<>-1) and (@ixb<>@xb)
begin
set @xym=0
set @xx='性别有误'
end
end
else select @xb=-1,@xb_c='未知'set @new_id_card=''
if @xym=1
begin
set @sum=0
set @i=18
while @i>=1
begin
select @sum=@sum+(power(2,(@i-1))%11)*cast(substring(@num,19-@i,1) as int)
select @i=@i-1
end
select @sum=@sum%11
select @verifycode=case @sum when 0 then '1' when 1 then '0' when 2 then 'X' else rtrim(12-@sum) end
if (@verifycode<>right(@vidcard,1)) and (len(@vidcard)=18)
begin
set @xym=0
set @xx='身份证校验码错误'
end
else set @new_id_card=@num+@verifycode
endset @csd=''
if @xym=1
begin
select @csd=isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,2)
if right(@vidcard,4)<>'0000' select @csd=@csd+ isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,4)
if right(@vidcard,2)<>'00' select @csd=@csd+ isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,6)
endset @xz=''
set @sx=''INSERT into @retidcardreports(idcard,xym,xx,newidcard,csny,nl_y,nl_m,nl_d,xb,xb_c,csd,xz,sx) select
@vidcard,@xym,@xx,@new_id_card,@birthday,@nl_y,@nl_m,@nl_d,@xb,@xb_c,@csd,@xz,@sxRETURN
endcreate Function fun_IdCard15to18(@vIdCode15 varchar(15))
returns varchar(18)
as
begin
declare @IDCode15to18 as varchar(18)
declare @i as int
declare @num as int
set @num=0
set @IDCode15to18=Left(@vIdCode15,6)+'19'+Right(@vIdCode15, 9)
set @i=18
while @i>=1
begin
select @num=@num+(power(2,(@i-1))%11)*cast(substring(@IDCode15to18,19-@i,1) as int)
select @i=@i-1
end
select @num=@num%11
select @IDCode15to18=@IDCode15to18+case @num when 0 then '1' when 1 then '0' when 2 then 'X' else rtrim(12-@num) end
return @IDCode15to18
end地区表可以参照网上的
? /*
参数说明
@vidcard:需要处理的身份证号
@ixb:身份证中需要对性别进行验证的1男0女-1表示不进行性别验证
@dtoday:今天日期,用于计算年龄和验证身份证的在效性返回表字段说明
idcard:原身份证号
xym:校验码1成功,0失败
xx:校验信息
newidcard:新身份证号,用于15位转18位
csny:出生年月
nl_y:虚岁年龄
nl_m:实际年龄,按月
nl_d:实际年龄,按天
xb:性别
csd:出生地
xz:星座
sx:生宵调用方式
declare @d datetime
set @d=getdate()
select * from dbo.fun_id_card('身份证号',-1,@d)
*/
create function fun_id_card(@vidcard varchar(18),@ixb int,@dtoday datetime)
returns @retidcardreports table(idcard varchar(18) primary key,
xym int not null,
xx varchar(100) not null,
newidcard varchar(18) not null,
csny varchar(8) not null,
nl_y int not null,
nl_m float not null,
nl_d float not null,
xb int not null,
xb_c varchar(4) not null,
csd varchar(100) not null,
xz varchar(20) not null,
sx varchar(4) not null
)
as
begin
declare @new_id_card varchar(18)
declare @birthday varchar(8)
declare @nl_y int
declare @nl_m float
declare @nl_d float
declare @xym int
declare @xx varchar(100)
declare @isdate int
declare @num varchar(17)
declare @i int
declare @sum int
declare @verifycode varchar(1)
declare @m int
declare @xb int
declare @xb_c varchar(4)
declare @csd varchar(100)
declare @xz varchar(10)
declare @sx varchar(2)set @xym=1
set @xx='身份证合法'If Len(@vidcard) < 15 Or Len(@vidcard) = 16 Or Len(@vidcard) = 17 Or Len(@vidcard) > 18
begin
set @xym=0
set @xx='身份证只允许有15位号码或18位号码'
endIf Len(@vidcard) = 18 set @num = left(@vidcard, 17)
else If Len(@vidcard) = 15 set @num = Left(@vidcard, 6) + '19' + Right(@vidcard, 9)
else set @num=''
If (IsNumeric(@num)=0) and (@xym=1)
begin
set @xym=0
set @xx='身份证除最后一位外，必须为数字'
Endif @xym=1 select @birthday=case len(@vidcard) when 18 then substring(@vidcard,7,8) when 15 then '19'+substring(@vidcard,7,6) end
else select @birthday= ''
select @isdate=isdate(@birthday)
if @xym=1
begin
if @isdate=0
begin
set @xym=0
set @xx='身份证出生日期无效'
set @birthday= ''
end
else if (DateDiff(yyyy,@dtoday,cast(@birthday as datetime))<-140) or
(DateDiff(dd,@dtoday,cast(@birthday as datetime))>1)
begin
set @xym=0
set @xx='身份证出生日期范围无效'
end
endif @xym=1
begin
select @nl_y=datediff(yyyy, @birthday, @dtoday)+1select @nl_m=case len(@vidcard) when 18 then cast(datediff(mm,substring(@vidcard,7,8),@dtoday) as real)/12
else cast(datediff(mm,substring(@vidcard,7,6),@dtoday) as real)/12 endselect @nl_d=datediff(yyyy, @birthday, @dtoday) +
1.0 * datediff(dd, dateadd(yyyy, datediff(yyyy, @birthday, @dtoday), @birthday),@dtoday) /
case when (year(@dtoday) % 4)+(year(@dtoday) % 400) = 0 then 366 else 365 end
end
else select @nl_y=0,@nl_m=0,@nl_d=0if @xym=1
begin
set @m=cast(right(@num,3) as int) % 2
select @xb=case @m when 0 then 0 else 1 end,@xb_c=case @m when 0 then '女' else '男' end
if (@ixb<>-1) and (@ixb<>@xb)
begin
set @xym=0
set @xx='性别有误'
end
end
else select @xb=-1,@xb_c='未知'set @new_id_card=''
if @xym=1
begin
set @sum=0
set @i=18
while @i>=1
begin
select @sum=@sum+(power(2,(@i-1))%11)*cast(substring(@num,19-@i,1) as int)
select @i=@i-1
end
select @sum=@sum%11
select @verifycode=case @sum when 0 then '1' when 1 then '0' when 2 then 'X' else rtrim(12-@sum) end
if (@verifycode<>right(@vidcard,1)) and (len(@vidcard)=18)
begin
set @xym=0
set @xx='身份证校验码错误'
end
else set @new_id_card=@num+@verifycode
endset @csd=''
if @xym=1
begin
select @csd=isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,2)
if right(@vidcard,4)<>'0000' select @csd=@csd+ isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,4)
if right(@vidcard,2)<>'00' select @csd=@csd+ isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,6)
endset @xz=''
set @sx=''INSERT into @retidcardreports(idcard,xym,xx,newidcard,csny,nl_y,nl_m,nl_d,xb,xb_c,csd,xz,sx) select
@vidcard,@xym,@xx,@new_id_card,@birthday,@nl_y,@nl_m,@nl_d,@xb,@xb_c,@csd,@xz,@sxRETURN
endcreate Function fun_IdCard15to18(@vIdCode15 varchar(15))
returns varchar(18)
as
begin
declare @IDCode15to18 as varchar(18)
declare @i as int
declare @num as int
set @num=0
set @IDCode15to18=Left(@vIdCode15,6)+'19'+Right(@vIdCode15, 9)
set @i=18
while @i>=1
begin
select @num=@num+(power(2,(@i-1))%11)*cast(substring(@IDCode15to18,19-@i,1) as int)
select @i=@i-1
end
select @num=@num%11
select @IDCode15to18=@IDCode15to18+case @num when 0 then '1' when 1 then '0' when 2 then 'X' else rtrim(12-@num) end
return @IDCode15to18
end地区表可以参照网上的
CK_Card CHECK ( len(fieldname)=15 or len(fieldname)=18 )ALTER TABLE tablename WITH NOCHECK ADD CONSTRAINT
CK_TEL CHECK ( len(fieldname)=11 or fieldname like "[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]" )
*****************************************************************************
欢迎使用CSDN论坛阅读器 : CSDN Reader(附全部源代码)
http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html