18-digit ID card verification code sharing ID card verification bit calculation using SQL

Calculation Method of ID card verification code

1. Multiply the 17 digits of the previous ID card number by different coefficients. The number of I-bits is [2 ^ (18-i)] mod11. The coefficients from the first digit to the second digit are 7 9 10 5 8 4 2 1 6 3 7 9 10 8 4 2;

2. Add the result of multiplying the 17-digit number and the coefficient;

3. How much is the remainder by adding and dividing by 11 ?;

4. The remainder may only contain the 11 numbers 0 1 2 3 4 5 6 7 8 9 10. The numbers corresponding to the last ID card respectively are 1 0X9 8 7 6 5 4 3 2;

Copy codeThe Code is as follows:
With t as (select '34052419800101001x 'id from dual)
Select id
From t
Where exists (select 1
From dual connect by level <= length (id)-1 -- 17
Having mod (sum (substr (id, level, 1) * power (2,18-level), 11) =
Case substr (id,-1, 1)
When '1' then 0
When '0' then 1
When 'X' then 2
Else
12-substr (id,-1, 1)
End );