Question

求SQL加入身份证号验证第18位的方法

我需要查询出身份证为空和身份证验证不通过的名单，因我是只读用户，不能使用编写函数和视图的方法，只能在查询器里解决，以下是我的语句，请高手帮我加一下，谢谢

use hballpopu_huanggangshi_2010

select *

from wistable0
where
(
wistable0.wisfield011 is null
or

Len(wistable0.wisfield011) <> 18
or

( ISDATE(SUBSTRING(wistable0.wisfield011,7,8))=0 OR LEFT(wistable0.wisfield011,17) NOT LIKE REPLICATE('[0-9]',17)
)

)

and DATEDIFF(year, wistable0.wisfield012, GETDATE())>= 6
order by unit_cun.u5

Answer 1

写个函数就行了：转自我的博客网页链接

ALTER  FUNCTION CheckSNID(@snid nvarchar(50))
RETURNS  bit  AS
BEGIN
declare @iRet bit
declare @id_num varchar(1)
declare @i int
declare @sn_sum int
declare @sn_Last varchar(1)
set @iRet=0
--判断是不是18位
if (len(@snid)<> 18) or (isnull(@snid,'')='')
goto ext
--第七位、第八位不是19 或者 20
if not ((substring(@snid,7,2)='19')  or (substring(@snid,7,2)='20'))
goto ext
--判断前17位是否都是数字
select @i=1,@id_num='',@sn_sum=0,@sn_Last=''
while @i<18
begin
--截取身份证中的一位
set @id_num=substring(@snid,@i,1)
if (@id_num<'0') or (@id_num>'9')
goto ext
select @sn_sum=(
case @i when 1 then @sn_sum+cast(@id_num as int)*7
when 2 then @sn_sum+cast(@id_num as int)*9
when 3 then @sn_sum+cast(@id_num as int)*10
when 4 then @sn_sum+cast(@id_num as int)*5
when 5 then @sn_sum+cast(@id_num as int)*8
when 6 then @sn_sum+cast(@id_num as int)*4
when 7 then @sn_sum+cast(@id_num as int)*2
when 8 then @sn_sum+cast(@id_num as int)*1
when 9 then @sn_sum+cast(@id_num as int)*6
when 10 then @sn_sum+cast(@id_num as int)*3
when 11 then @sn_sum+cast(@id_num as int)*7
when 12 then @sn_sum+cast(@id_num as int)*9
when 13 then @sn_sum+cast(@id_num as int)*10
when 14 then @sn_sum+cast(@id_num as int)*5
when 15 then @sn_sum+cast(@id_num as int)*8
when 16 then @sn_sum+cast(@id_num as int)*4
when 17 then @sn_sum+cast(@id_num as int)*2 end)
set @i=@i+1
end
--根据取余判断最后位
set @sn_sum=@sn_sum%11
select @sn_Last=
(case @sn_sum when 0 then '1'
when 1 then '0'
when 2 then 'X'
when 3 then '9'
when 4 then '8'
when 5 then '7'
when 6 then '6'
when 7 then '5'
when 8 then '4'
when 9 then '3'
when 10 then '2' end)
if (@sn_Last='X')
BEGIN
if (substring(@snid,18,1)='X') or (substring(@snid,18,1)='x')
set @iRet=1
END
ELSE
if (@sn_Last=substring(@snid,18,1))
set @iRet=1
ext:
return @iRet
END;

Answer 2

身份证号码是每一个公民终身不变的号码，不要查询使用他人的身份号码，请使用自己的身份号码，否则会负法律责任。

Answer 3

结果集取到之后，在应用层面解决是不是也行？