身份证件号码校验规则sql
时间: 2023-08-30 10:09:38 浏览: 49
身份证件号码校验规则是指对身份证号码进行合法性校验,一般要求身份证号码必须符合国家规定的格式,并且校验位也要正确。
以下是身份证件号码校验规则的 SQL 代码示例:
```sql
-- 获取身份证号码的前 17 位数字
SELECT SUBSTR(id_card, 1, 17) AS id_card_17
FROM users;
-- 获取身份证号码的最后一位校验位
SELECT SUBSTR(id_card, 18, 1) AS id_card_check
FROM users;
-- 计算身份证号码的校验位
SELECT CASE
WHEN MOD(SUBSTR(id_card, 1, 1) * 7 + SUBSTR(id_card, 2, 1) * 9 + SUBSTR(id_card, 3, 1) * 10 + SUBSTR(id_card, 4, 1) * 5 + SUBSTR(id_card, 5, 1) * 8 + SUBSTR(id_card, 6, 1) * 4 + SUBSTR(id_card, 7, 1) * 2 + SUBSTR(id_card, 8, 1) * 1 + SUBSTR(id_card, 9, 1) * 6 + SUBSTR(id_card, 10, 1) * 3 + SUBSTR(id_card, 11, 1) * 7 + SUBSTR(id_card, 12, 1) * 9 + SUBSTR(id_card, 13, 1) * 10 + SUBSTR(id_card, 14, 1) * 5 + SUBSTR(id_card, 15, 1) * 8 + SUBSTR(id_card, 16, 1) * 4 + SUBSTR(id_card, 17, 1) * 2, 11) = 0 THEN '0'
WHEN MOD(SUBSTR(id_card, 1, 1) * 7 + SUBSTR(id_card, 2, 1) * 9 + SUBSTR(id_card, 3, 1) * 10 + SUBSTR(id_card, 4, 1) * 5 + SUBSTR(id_card, 5, 1) * 8 + SUBSTR(id_card, 6, 1) * 4 + SUBSTR(id_card, 7, 1) * 2 + SUBSTR(id_card, 8, 1) * 1 + SUBSTR(id_card, 9, 1) * 6 + SUBSTR(id_card, 10, 1) * 3 + SUBSTR(id_card, 11, 1) * 7 + SUBSTR(id_card, 12, 1) * 9 + SUBSTR(id_card, 13, 1) * 10 + SUBSTR(id_card, 14, 1) * 5 + SUBSTR(id_card, 15, 1) * 8 + SUBSTR(id_card, 16, 1) * 4 + SUBSTR(id_card, 17, 1) * 2, 11) = 1 THEN 'X'
ELSE CAST(11 - MOD(SUBSTR(id_card, 1, 1) * 7 + SUBSTR(id_card, 2, 1) * 9 + SUBSTR(id_card, 3, 1) * 10 + SUBSTR(id_card, 4, 1) * 5 + SUBSTR(id_card, 5, 1) * 8 + SUBSTR(id_card, 6, 1) * 4 + SUBSTR(id_card, 7, 1) * 2 + SUBSTR(id_card, 8, 1) * 1 + SUBSTR(id_card, 9, 1) * 6 + SUBSTR(id_card, 10, 1) * 3 + SUBSTR(id_card, 11, 1) * 7 + SUBSTR(id_card, 12, 1) * 9 + SUBSTR(id_card, 13, 1) * 10 + SUBSTR(id_card, 14, 1) * 5 + SUBSTR(id_card, 15, 1) * 8 + SUBSTR(id_card, 16, 1) * 4 + SUBSTR(id_card, 17, 1) * 2, 11) AS CHAR)
END AS id_card_check
FROM users;
-- 校验身份证号码的合法性
SELECT CASE
WHEN REGEXP_LIKE(id_card, '^[1-9]\d{5}(19|20)\d{2}(0[1-9]|1[0-2])([0-2][1-9]|[1-3]\d|4[0-6]|5[0-3])\d{3}([0-9]|X)$') AND SUBSTR(id_card, 18, 1) = (
SELECT CASE
WHEN MOD(SUBSTR(id_card, 1, 1) * 7 + SUBSTR(id_card, 2, 1) * 9 + SUBSTR(id_card, 3, 1) * 10 + SUBSTR(id_card, 4, 1) * 5 + SUBSTR(id_card, 5, 1) * 8 + SUBSTR(id_card, 6, 1) * 4 + SUBSTR(id_card, 7, 1) * 2 + SUBSTR(id_card, 8, 1) * 1 + SUBSTR(id_card, 9, 1) * 6 + SUBSTR(id_card, 10, 1) * 3 + SUBSTR(id_card, 11, 1) * 7 + SUBSTR(id_card, 12, 1) * 9 + SUBSTR(id_card, 13, 1) * 10 + SUBSTR(id_card, 14, 1) * 5 + SUBSTR(id_card, 15, 1) * 8 + SUBSTR(id_card, 16, 1) * 4 + SUBSTR(id_card, 17, 1) * 2, 11) = 0 THEN '0'
WHEN MOD(SUBSTR(id_card, 1, 1) * 7 + SUBSTR(id_card, 2, 1) * 9 + SUBSTR(id_card, 3, 1) * 10 + SUBSTR(id_card, 4, 1) * 5 + SUBSTR(id_card, 5, 1) * 8 + SUBSTR(id_card, 6, 1) * 4 + SUBSTR(id_card, 7, 1) * 2 + SUBSTR(id_card, 8, 1) * 1 + SUBSTR(id_card, 9, 1) * 6 + SUBSTR(id_card, 10, 1) * 3 + SUBSTR(id_card, 11, 1) * 7 + SUBSTR(id_card, 12, 1) * 9 + SUBSTR(id_card, 13, 1) * 10 + SUBSTR(id_card, 14, 1) * 5 + SUBSTR(id_card, 15, 1) * 8 + SUBSTR(id_card, 16, 1) * 4 + SUBSTR(id_card, 17, 1) * 2, 11) = 1 THEN 'X'
ELSE CAST(11 - MOD(SUBSTR(id_card, 1, 1) * 7 + SUBSTR(id_card, 2, 1) * 9 + SUBSTR(id_card, 3, 1) * 10 + SUBSTR(id_card, 4, 1) * 5 + SUBSTR(id_card, 5, 1) * 8 + SUBSTR(id_card, 6, 1) * 4 + SUBSTR(id_card, 7, 1) * 2 + SUBSTR(id_card, 8, 1) * 1 + SUBSTR(id_card, 9, 1) * 6 + SUBSTR(id_card, 10, 1) * 3 + SUBSTR(id_card, 11, 1) * 7 + SUBSTR(id_card, 12, 1) * 9 + SUBSTR(id_card, 13, 1) * 10 + SUBSTR(id_card, 14, 1) * 5 + SUBSTR(id_card, 15, 1) * 8 + SUBSTR(id_card, 16, 1) * 4 + SUBSTR(id_card, 17, 1) * 2, 11) AS CHAR)
END
) THEN '合法'
ELSE '不合法'
END AS id_card_validity
FROM users;
```
其中,`id_card` 是要验证的身份证号码字段。这个 SQL 代码示例可以获取身份证号码的前 17 位数字、最后一位校验位,计算身份证号码的校验位,并检查身份证号码是否符合国家规定的格式和校验位是否正确,最终返回身份证件号码的合法性。