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
  if (substring(@snid,7,2)<>‘19‘)
  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‘) and (@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;

用SQL函数判断是否有效18位身份证号,布布扣,bubuko.com

用SQL函数判断是否有效18位身份证号