mysql约束身份证号18位_用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'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;