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

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;