alter function dbo.IDTrue--验证十八位身份证是否正确
(
@ID varchar(18)
)
returns bit
as
begin
--验证格式是否正确
declare @RES bit
declare @W table (rn int,val int)
insert into @W(rn,val)
select 1,7
union select 2,9
union select 3,10
union select 4,5
union select 5,8
union select 6,4
union select 7,2
union select 8,1
union select 9,6
union select 10,3
union select 11,7
union select 12,9
union select 13,10
union select 14,5
union select 15,8
union select 16,4
union select 17,2
union select 18,1
declare @A table (rwn int ,val varchar(1))
insert into @A(rwn,val)
select 0,'1'
insert into @A(rwn,val)
select 1,'0'
insert into @A(rwn,val)
select 2,'X'
insert into @A(rwn,val)
select 3,'9'
insert into @A(rwn,val)
select 4,'8'
insert into @A(rwn,val)
select 5,'7'
insert into @A(rwn,val)
select 6,'6'
insert into @A(rwn,val)
select 7,'5'
insert into @A(rwn,val)
select 8,'4'
insert into @A(rwn,val)
select 9,'3'
insert into @A(rwn,val)
select 10,'2'
declare @i int
declare @j int
declare @S intif len(@ID)=18
begin
select @S=0,@i=1
declare @IDI int
declare @WI int
declare @PII varchar(1)
while @i<=17
begin
select @PII=substring(@ID,@i,1)
if @PII like '[0-9]'
begin
select @IDI=convert(int,@PII)
select @WI=val from @W where rn=@i
select @j=@IDI*@WI
select @S=@S+@j
select @i=@i+1
end
else
begin
select @RES=0
return @RES
end end
select @S=@S % 11
select @IDI=convert(int,substring(@ID,18,1))
declare @pi varchar(1)
select @pi=val from @A where rwn =@S
if @pi=@IDI
select @RES=1
else
select @RES=0
end
else
begin
select @RES=0
end
return @RES
end
go
declare @pc bit
select @pc=dbo.IDTrue('420324198101031224')
select @pc
goalter function SFZ15To18--十五位升十八
(
@sfz varchar(15)
)
returns varchar(18)
as
begin
declare @W table (rn int,val int)
insert into @W(rn,val)
select 1,7
union select 2,9
union select 3,10
union select 4,5
union select 5,8
union select 6,4
union select 7,2
union select 8,1
union select 9,6
union select 10,3
union select 11,7
union select 12,9
union select 13,10
union select 14,5
union select 15,8
union select 16,4
union select 17,2
union select 18,1
declare @A table (rwn int ,val varchar(1))
insert into @A(rwn,val)
select 0,'1'
insert into @A(rwn,val)
select 1,'0'
insert into @A(rwn,val)
select 2,'X'
insert into @A(rwn,val)
select 3,'9'
insert into @A(rwn,val)
select 4,'8'
insert into @A(rwn,val)
select 5,'7'
insert into @A(rwn,val)
select 6,'6'
insert into @A(rwn,val)
select 7,'5'
insert into @A(rwn,val)
select 8,'4'
insert into @A(rwn,val)
select 9,'3'
insert into @A(rwn,val)
select 10,'2'
declare @NEWID varchar(18)
select @NEWID=substring(@sfz,1,6)+'19'+substring(@sfz,7,9)
declare @i int
declare @j int
declare @S int
select @S=0,@i=1
declare @IDI int
declare @WI int
declare @PII varchar(1)
while @i<=17
begin
select @PII=substring(@NEWID,@i,1)
if @PII like '[0-9]'
begin
select @IDI=convert(int,@PII)
select @WI=val from @W where rn=@i
select @j=@IDI*@WI
select @S=@S+@j
select @i=@i+1
end
else
begin
return ''
end
end
select @S=@S % 11
declare @pi varchar(1)
select @pi=val from @A where rwn =@S
select @NEWID=@NEWID+@pi
return @NEWID end
go
select dbo.SFZ15To18('420324810103153')
go
--日期是否正确
alter function ChkYMD(
@y int,
@m tinyint,
@d tinyint,
@cy int
)
returns bit
as
begin
declare @res bit
select @res=1
if @y<1900 or @y>@cy
begin
select @res=0
return @res
end
if @m=1 or @m=3 or @m=5 or @m=7 or @m=8 or @m=10 or @m=12
begin
if (@d<1) or (@d>31)
begin
select @res=0
return @res
end
end
if @m=2
begin
if ((@y%4)=0) and ((@y % 100)<>0) or ( (@y % 400)=0 )
begin--闰年
if (@d<1) or (@d>29)
begin
select @res=0
return @res
end
end else
begin
if (@d<1) or (@d>28)
begin
select @res=0
return @res
end
end
end
if @m=4 or @m=6 or @m=9 or @m=11
begin
if (@d<1) or (@d>30)
begin
select @res=0
return @res
end
end
return @res
end
go
select dbo.chkymd(1981,1,3,Year(getdate()))
go
/*
y:年,m:月,d:日。在参数都只传入相应的整数
返回值:0 星期一
1 星期2
2 星期3
3 星期4
4 星期5
5 星期6
6 星期7
*/alter function GetWeekDay(
@y int,
@m int,
@d int
)
returns tinyint
as
begin
declare @a tinyint
select @a=7
if @m=1 or @m=2
begin
select @m=@m+12
select @y=@y-1
end
select @a=(@d+2*@m+3*(@m+1)/5+@y+@y/4-@y/100+@y/400)%7;
return @a
end
go
select dbo.getweekday(2004,12,10)
(
@ID varchar(18)
)
returns bit
as
begin
--验证格式是否正确
declare @RES bit
declare @W table (rn int,val int)
insert into @W(rn,val)
select 1,7
union select 2,9
union select 3,10
union select 4,5
union select 5,8
union select 6,4
union select 7,2
union select 8,1
union select 9,6
union select 10,3
union select 11,7
union select 12,9
union select 13,10
union select 14,5
union select 15,8
union select 16,4
union select 17,2
union select 18,1
declare @A table (rwn int ,val varchar(1))
insert into @A(rwn,val)
select 0,'1'
insert into @A(rwn,val)
select 1,'0'
insert into @A(rwn,val)
select 2,'X'
insert into @A(rwn,val)
select 3,'9'
insert into @A(rwn,val)
select 4,'8'
insert into @A(rwn,val)
select 5,'7'
insert into @A(rwn,val)
select 6,'6'
insert into @A(rwn,val)
select 7,'5'
insert into @A(rwn,val)
select 8,'4'
insert into @A(rwn,val)
select 9,'3'
insert into @A(rwn,val)
select 10,'2'
declare @i int
declare @j int
declare @S intif len(@ID)=18
begin
select @S=0,@i=1
declare @IDI int
declare @WI int
declare @PII varchar(1)
while @i<=17
begin
select @PII=substring(@ID,@i,1)
if @PII like '[0-9]'
begin
select @IDI=convert(int,@PII)
select @WI=val from @W where rn=@i
select @j=@IDI*@WI
select @S=@S+@j
select @i=@i+1
end
else
begin
select @RES=0
return @RES
end end
select @S=@S % 11
select @IDI=convert(int,substring(@ID,18,1))
declare @pi varchar(1)
select @pi=val from @A where rwn =@S
if @pi=@IDI
select @RES=1
else
select @RES=0
end
else
begin
select @RES=0
end
return @RES
end
go
declare @pc bit
select @pc=dbo.IDTrue('420324198101031224')
select @pc
goalter function SFZ15To18--十五位升十八
(
@sfz varchar(15)
)
returns varchar(18)
as
begin
declare @W table (rn int,val int)
insert into @W(rn,val)
select 1,7
union select 2,9
union select 3,10
union select 4,5
union select 5,8
union select 6,4
union select 7,2
union select 8,1
union select 9,6
union select 10,3
union select 11,7
union select 12,9
union select 13,10
union select 14,5
union select 15,8
union select 16,4
union select 17,2
union select 18,1
declare @A table (rwn int ,val varchar(1))
insert into @A(rwn,val)
select 0,'1'
insert into @A(rwn,val)
select 1,'0'
insert into @A(rwn,val)
select 2,'X'
insert into @A(rwn,val)
select 3,'9'
insert into @A(rwn,val)
select 4,'8'
insert into @A(rwn,val)
select 5,'7'
insert into @A(rwn,val)
select 6,'6'
insert into @A(rwn,val)
select 7,'5'
insert into @A(rwn,val)
select 8,'4'
insert into @A(rwn,val)
select 9,'3'
insert into @A(rwn,val)
select 10,'2'
declare @NEWID varchar(18)
select @NEWID=substring(@sfz,1,6)+'19'+substring(@sfz,7,9)
declare @i int
declare @j int
declare @S int
select @S=0,@i=1
declare @IDI int
declare @WI int
declare @PII varchar(1)
while @i<=17
begin
select @PII=substring(@NEWID,@i,1)
if @PII like '[0-9]'
begin
select @IDI=convert(int,@PII)
select @WI=val from @W where rn=@i
select @j=@IDI*@WI
select @S=@S+@j
select @i=@i+1
end
else
begin
return ''
end
end
select @S=@S % 11
declare @pi varchar(1)
select @pi=val from @A where rwn =@S
select @NEWID=@NEWID+@pi
return @NEWID end
go
select dbo.SFZ15To18('420324810103153')
go
--日期是否正确
alter function ChkYMD(
@y int,
@m tinyint,
@d tinyint,
@cy int
)
returns bit
as
begin
declare @res bit
select @res=1
if @y<1900 or @y>@cy
begin
select @res=0
return @res
end
if @m=1 or @m=3 or @m=5 or @m=7 or @m=8 or @m=10 or @m=12
begin
if (@d<1) or (@d>31)
begin
select @res=0
return @res
end
end
if @m=2
begin
if ((@y%4)=0) and ((@y % 100)<>0) or ( (@y % 400)=0 )
begin--闰年
if (@d<1) or (@d>29)
begin
select @res=0
return @res
end
end else
begin
if (@d<1) or (@d>28)
begin
select @res=0
return @res
end
end
end
if @m=4 or @m=6 or @m=9 or @m=11
begin
if (@d<1) or (@d>30)
begin
select @res=0
return @res
end
end
return @res
end
go
select dbo.chkymd(1981,1,3,Year(getdate()))
go
/*
y:年,m:月,d:日。在参数都只传入相应的整数
返回值:0 星期一
1 星期2
2 星期3
3 星期4
4 星期5
5 星期6
6 星期7
*/alter function GetWeekDay(
@y int,
@m int,
@d int
)
returns tinyint
as
begin
declare @a tinyint
select @a=7
if @m=1 or @m=2
begin
select @m=@m+12
select @y=@y-1
end
select @a=(@d+2*@m+3*(@m+1)/5+@y+@y/4-@y/100+@y/400)%7;
return @a
end
go
select dbo.getweekday(2004,12,10)
对象名‘dbo.IDTrue’无效
嘿嘿嘿嘿,上周组织部门同事去杭州玩,就没有上来. : )
CSDN上还有人记得我,好感动........
vivianfdlpw
对象名‘dbo.IDTrue’无效
-----------------把vivianfdlpw给的所有的Alter关键字改成Create关键字就可以了.
那些函数你还没有创建就去更改,当然会报这个错了.
GO
SET ANSI_NULLS ON
GOALTER function checksfz (@sfz varchar (18))
returns int
as
/*
** 身份证验证
** by IBN
** 参数为一个,即字符型数据@sfz
**
** 返回值:
**
** 0 验证成功
** 1 长度不正确,正确长度是15或18
** 2 年份不正确,7-8 或7-10位
** 3 月份不正确,9-10 或11-12位
** 4 日期份不正确,11-12 或13-14位
** 5 数字不正确,含非数字字符
** 6 校验码不正确,第18位
*/
begin
declare @l int, --length
@i int, --position
@x int, --number
@y int, --base
@E int --sumset @l = len(@sfz) --input length
set @E = 0 --initif (@l<>15 and @l<>18)
return 1 -- error1 : length is not 15 or 18if @l = 15
begin
if not substring(@sfz,7,2) between '00' and '99'
return 2 -- error2 : year not correct
if not substring(@sfz,9,2) between '01' and '12'
return 3 -- error3 : month not correct
if not substring(@sfz,11,2) between '01' and '31'
return 4 -- error4 : date not correct
set @i = 15 --begin position
end
else --@l = 18
begin
if not substring(@sfz,7,4) between '1900' and '2099'
return 2 -- error2 : year not correct
if not substring(@sfz,11,2) between '01' and '12'
return 3 -- error3 : month not correct
if not substring(@sfz,13,2) between '01' and '31'
return 4 -- error4 : date not correct
if (not substring(@sfz,18,1) between '0' and '9') and
(not substring(@sfz,18,1)='X')
return 5 -- error5 : not all number
set @i = 17
endwhile @i>0
begin
if (not substring(@sfz,@i,1) between '0' and '9')
return 5 -- error5 : not all number
set @x = cast(substring(@sfz,@i,1) as int) --get number
set @y = power(2,(@l-@i))%11 --get base 2^(n-1)
set @E = @E + @x*@y --sum E(x*y)
set @i = @i -1 --next position desc
endif @l = 15 return 0 --correct
set @E = @E%11
set @E= (12-@E)%11
if (isnumeric(right(@sfz,1)) = 0) set @i = 10 -- Last is X
else set @i = cast(right(@sfz,1) as int)
if @E <> @i return 6 -- error6 : checksum error !!!
return 0 -- correct
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO这是我写的,将就可用.仅供参考
地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。生日期码表示编码对象出生的年、月、日,其中年份用四位数字表示,年、月、日之间不用分隔符。顺序码表示同一地址码所标识的区域范围内,对同年、月、日出生的人员编定的顺序号。顺序码的奇数分给男性,偶数分给女性。校验码是根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。下面举例说明该计算方法。
15位的身份证编码首先把出生年扩展为4位,简单的就是增加一个19,但是这对于1900年出生的人不使用(这样的寿星不多了)
某男性公民身份号码本体码为34052419800101001,首先按照公式⑴计算:
∑(ai×Wi)(mod 11)……………………………………(1)
公式(1)中:
i----表示号码字符从由至左包括校验码在内的位置序号;
ai----表示第i位置上的号码字符值;
Wi----示第i位置上的加权因子,其数值依据公式Wi=2(n-1)(mod 11)计算得出。
i 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1
ai 3 4 0 5 2 4 1 9 8 0 0 1 0 1 0 0 1 a1
Wi 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 1
ai×Wi 21 36 0 25 16 16 2 9 48 0 0 9 0 5 0 0 2 a1
根据公式(1)进行计算:
∑(ai×Wi) =(21+36+0+25+16+16+2+9+48++0+0+9+0+5+0+0+2) = 189
189 ÷ 11 = 17 + 2/11
∑(ai×Wi)(mod 11) = 2
然后根据计算的结果,从下面的表中查出相应的校验码,其中X表示计算结果为10:
∑(ai×WI)(mod 11) 0 1 2 3 4 5 6 7 8 9 10
校验码字符值ai 1 0 X 9 8 7 6 5 4 3 2