• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

身份证验证_sqlserver

sqlserver 搞代码 3年前 (2018-06-16) 69次浏览 已收录 0个评论

alter  function  dbo.IDTrue–验证十八位身份证是否正确
(
  @ID  varchar(18)
)

http://www.gaodaima.com/35810.html身份证验证_sqlserver

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  int

if 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   [email protected]
         select  @[email protected][email protected]
         select  @[email protected][email protected]
         select  @[email protected]+1
        end
       else
        begin
              select  @RES=0
               return  @RES
        end

     end
  select @[email protected] % 11
  select  @IDI=convert(int,substring(@ID,18,1))
  declare  @pi   varchar(1)
  select  @pi=val from  @A where  rwn [email protected] 
  if   @[email protected]
      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
go

alter 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   [email protected]
          select  @[email protected][email protected]
          select  @[email protected][email protected]
          select  @[email protected]+1
         end
         else
         begin
          return ”
         end
   end
 select @[email protected] % 11
 declare  @pi   varchar(1)
 select  @pi=val from  @A where  rwn [email protected]
 select  @[email protected][email protected]
 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  @[email protected]+12
        select  @[email protected]
     end
  select @a=(@[email protected]+3*(@m+1)[email protected][email protected][email protected][email protected]/400)%7;
  return @a
end
go
select  dbo.getweekday(2004,12,10)

欢迎大家阅读《身份证验证_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址