T-SQL验证身份证号码通用版可以有效解决身份证验证时所经常遇到的身份证号字段中存有非法字符而导致数据转换失败的问题。该SQL代码可以有效校验15位和18位身份证号码的正确性。
/* 使用时替换 数据表名称 为要查验的表，字段身份证号*/
IF OBJECT_ID(N'tempdb.dbo.#TEMP_RESULT') IS NOT NULL DROP TABLE #TEMP_RESULT;
IF OBJECT_ID(N'tempdb.dbo.#TEMP_RESULT1') IS NOT NULL DROP TABLE #TEMP_RESULT1;
IF OBJECT_ID(N'tempdb.dbo.#TEMP_RESULT2') IS NOT NULL DROP TABLE #TEMP_RESULT2;
WITH G AS (
SELECT * FROM 数据表名称 WHERE (LEN(REPLACE([身份证号],' ',''))!=18
AND LEN(REPLACE([身份证号],' ',''))!=15)
OR [身份证号] IS NULL
OR REPLACE([身份证号],' ','')=''
OR [身份证号] LIKE '%.%'),
K AS (
SELECT * FROM (
SELECT * FROM 数据表名称 EXCEPT
SELECT * FROM G) a WHERE
ASCII(SUBSTRING(身份证号,1,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,2,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,3,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,4,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,5,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,6,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,7,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,8,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,9,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,10,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,11,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,12,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,13,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,14,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(身份证号,15,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(CASE WHEN LEN(身份证号)>15 THEN 身份证号 ELSE '111111111111111111' END,16,1)) NOT BETWEEN 48 AND 57
OR ASCII(SUBSTRING(CASE WHEN LEN(身份证号)>15 THEN 身份证号 ELSE '111111111111111111' END,17,1)) NOT BETWEEN 48 AND 57
OR (ASCII(SUBSTRING(CASE WHEN LEN(身份证号)>15 THEN 身份证号 ELSE '111111111111111111' END,18,1)) != 88
AND ASCII(SUBSTRING(CASE WHEN LEN(身份证号)>15 THEN 身份证号 ELSE '111111111111111111' END,18,1)) !=120
AND ASCII(SUBSTRING(CASE WHEN LEN(身份证号)>15 THEN 身份证号 ELSE '111111111111111111' END,18,1)) NOT BETWEEN 48 AND 57))
SELECT * INTO #TEMP_RESULT1 FROM (
SELECT * FROM G
UNION
SELECT * FROM K
) a;
WITH Q_TEMP AS(
SELECT * FROM (
SELECT * FROM 数据表名称 EXCEPT
SELECT * FROM #TEMP_RESULT1
) a
WHERE LEN(身份证号)=18),
W_TEMP AS(
SELECT *,校验码=SUBSTRING('10X98765432',
CONVERT(INT,((CONVERT(INT,SUBSTRING(身份证号,1,1))*7+CONVERT(INT,SUBSTRING(身份证号,2,1))*9+CONVERT(INT,SUBSTRING(身份证号,3,1))*10+
CONVERT(INT,SUBSTRING(身份证号,4,1))*5+CONVERT(INT,SUBSTRING(身份证号,5,1))*8+CONVERT(INT,SUBSTRING(身份证号,6,1))*4+
CONVERT(INT,SUBSTRING(身份证号,7,1))*2+CONVERT(INT,SUBSTRING(身份证号,8,1))*1+CONVERT(INT,SUBSTRING(身份证号,9,1))*6+
CONVERT(INT,SUBSTRING(身份证号,10,1))*3+CONVERT(INT,SUBSTRING(身份证号,11,1))*7+CONVERT(INT,SUBSTRING(身份证号,12,1))*9+
CONVERT(INT,SUBSTRING(身份证号,13,1))*10+CONVERT(INT,SUBSTRING(身份证号,14,1))*5+CONVERT(INT,SUBSTRING(身份证号,15,1))*8+
CONVERT(INT,SUBSTRING(身份证号,16,1))*4+CONVERT(INT,SUBSTRING(身份证号,17,1))*2)%11))+1,1) FROM Q_TEMP)
SELECT * INTO #TEMP_RESULT FROM W_TEMP;
SELECT * INTO #TEMP_RESULT2 FROM #TEMP_RESULT WHERE 校验码 != UPPER(RIGHT(身份证号,1));
ALTER TABLE #TEMP_RESULT2 DROP COLUMN 校验码;
IF OBJECT_ID(N'tempdb.dbo.#身份证不正确记录') IS NOT NULL DROP TABLE #身份证不正确记录;
SELECT * INTO #身份证不正确记录 FROM (
SELECT * FROM #TEMP_RESULT1
UNION
SELECT * FROM #TEMP_RESULT2
) a;
IF OBJECT_ID(N'tempdb.dbo.#身份证正确记录') IS NOT NULL DROP TABLE #身份证正确记录;
SELECT * INTO #身份证正确记录 FROM (
SELECT * FROM 数据表名称
EXCEPT
SELECT * FROM #身份证不正确记录
)a;
SELECT 身份证不正确记录数=COUNT(*) FROM #身份证不正确记录
SELECT 身份证正确记录数=COUNT(*) FROM #身份证正确记录