for example: card no. = I1363861
1) if last_digit in ('0'..'9') check_sum = last_digit, if last_digit = 'A', check_sum = 10
else validation fail
check_sum = 1;
2) total = (ascii_value(first_digit) - 64) * 8
total = (ascii('I') - 64) * 8 = 72
3) for i = 2 to 7 { total += StrToInt(id[i]) * (9 - i); }
total = 72 + 1 * 7 + 3 * 6 + 6 * 5 + 3 * 4 + 8 * 3 + 6 * 2 = 175
4) remainder = total - (total / 11) * 11
remainder = 175 - (175 / 11) * 11 = 10
5) if remainder <> 0 { remainder = 11 - remainder }
remainder = 11 - 10 = 1
6) result = (check_sum == remainder ? true : false);
check_sum == remainder, return 1
--------------------------------------------------------------
if object_id('is_valid_hk_id') is not null
drop function is_valid_hk_id
go
create function is_valid_hk_id(@id_no nvarchar(10))
returns bit
as
begin
declare @check_sum int, @total int, @i int, @remainder int, @check_char char
declare @result bit
declare @id nvarchar(10)
set @id = upper(ltrim(rtrim(@id_no)))
set @id = replace(@id, '(', '')
set @id = replace(@id, ')', '')
if len(@id) <> 8
return 0
set @check_char = substring(@id, 8, 1)
if isnumeric(@check_char) = 1 set @check_sum = cast(@check_char as int)
else if @check_char = 'A' set @check_sum = 10
else return 0
if isnumeric(substring(@id, 2, 6)) = 0
return 0
set @total = (ascii(substring(@id, 1, 1)) - 64) * 8
set @i = 2
while @i <= 7
begin
set @total = @total + cast(substring(@id, @i, 1) as int) * (9 - @i)
set @i = @i + 1
end
set @remainder = @total - (@total / 11) * 11
if @remainder <> 0
set @remainder = 11 - @remainder
return case when @check_sum = @remainder then 1 else 0 end
end
go