T-SQL验证身份证号码通用版

发布于 作者 量尺寸留下评论

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 #身份证正确记录

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注