Using SQL to achieve 18-bit ID card verification code sharing ID card check bit calculation _mssql

Calculation method of ID card check code

1, the previous ID number 17 digits multiplied by different coefficients. The number of the I-bit corresponds to [2^ (18-i)]mod11. The coefficients from first to 17th are: 7 9 10 5 8 4 2 1 6 3 7 9 10 5-8 4 2;

2, the 17-digit numbers and coefficients multiplied by the result of the addition;

3, with added out and divided by 11, see the remainder is how much? ；

4. The surplus can only have 0 1 2 3 4 5 6 7 8 9 10 of these 11 digits. The corresponding number of the last ID card is 1 0 X 9 8 7 6 5 4 3 2;

Copy Code code 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);