Check whether the ID card number is correct using SQL

Verify whether the ID card number complies with the second generation ID card specification based on the provided ID card number information. The region code can be downloaded online. The database is DB2, but the general ID card number 18th-bit verification algorithm can be checked from the internet. the specific verification algorithm is as follows: 1. multiply the 17-digit ID card number in front by different coefficients. The number of I-bits is [2 ^ (

Verify whether the ID card number complies with the second generation ID card specification based on the provided ID card number information. The region code can be downloaded online. The database is DB2, but the general ID card number 18th-bit verification algorithm can be checked from the internet. the specific verification algorithm is as follows: 1. multiply the 17-digit ID card number in front by different coefficients. The number of I-bits is [2 ^ (

Verify whether the ID card number complies with the second generation ID card specification based on the provided ID card number information. The region code can be downloaded online.
The database is DB2, but it can be used in visual testing.
The ID card number 18th-bit verification algorithm is obtained from the internet. the specific verification algorithm is as follows:
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; <无>

Select/* pspt_id is the user ID number */. pspt_id/* determine whether the user ID card meets the rules */, case/* determine whether the ID card number is 18 characters */when length (. pspt_id) <> '18 'then' incorrect number of digits '/* determines whether the first 17 digits of the ID card number contain characters other than numbers */when translate (substr (. pspt_id, 0123456789), '', '20140901 ') <> ''then': the first 17 digits of the ID card number are incorrectly formatted. '/* determines whether the year of the ID card is within the valid range */when substr (. pspt_id, 1900) not between '000000' and '000000' then' ID card year error'/* determine whether the month of the ID card is within a reasonable range */when substr (. pspt_id, 11, 2) not between '01 'and '12' then' ID card month error'/* determine whether the ID card date is within a reasonable range */when substr (. pspt_id, 13, 2) not between 1 and day (to_date (char (substr (. pspt_id, 7,4) | '-' | substr (. pspt_id, 11, 2) | '-01', 10), 'yyyy-mm-DD') + 1 month-1 day) then 'incorrect ID card date'/* determines whether the 18th bits of the ID card number comply with the verification rules */when mod (substr (. pspt_id, 1, 1) * 7 + substr (. pspt_id, 2, 1) * 9 + substr (. pspt_id, 3, 1) * 10 + substr (. pspt_id, 4, 1) * 5 + substr (. pspt_id, 5, 1) * 8 + substr (. pspt_id, 6, 1) * 4 + substr (. pspt_id, 7,1) * 2 + substr (. pspt_id, 8, 1) * 1 + substr (. pspt_id, 9, 1) * 6 + substr (. pspt_id, 10, 1) * 3 + substr (. pspt_id, 11,1) * 7 + substr (. pspt_id, 12, 1) * 9 + substr (. pspt_id, 13, 1) * 10 + substr (. pspt_id, 14,1) * 5 + substr (. pspt_id, 15,1) * 8 + substr (. pspt_id, 16, 1) * 4 + substr (. pspt_id, 17,1) * 2), 11) <> (case when substr (. pspt_id,) = '1' then '0' when substr (. pspt_id,) = '0' then '1' when substr (. pspt_id,) in ('X', 'x') then'2' when substr (. pspt_id,) = '9' then '3' when substr (. pspt_id,) = '8' then '4' when substr (. pspt_id,) = '7' then '5' when substr (. pspt_id,) = '6' then '6' when substr (. pspt_id,) = '5' then '7' when substr (. pspt_id,) = '4' then '8' when substr (. pspt_id,) = '3' then '9' when substr (. pspt_id,) = '2' then '10' end) then' ID card verification error '/* determine whether the region code of the ID card number complies with the rules */when B. county_sar_code is null then 'District/county code verification error 'else' valid real-name customer' end/* User information table, contains the ID card number information to be queried */from usr_info a/* the ID card's administrative region code table, which can be downloaded from the official website of the Bureau of Statistics and the Internet, set county_sar_code to 6-digit administrative code */left join csounty_sar B on substr (. pspt_id, 1, 6) = B. county_sar_code