HongKong ID Card No. Validation

 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