Question

sql语句验证虚假身份证，出现“从数据类型 nvarchar 转换为 float 时出错”，求高手解答

审计AO中核实“excel表”虚假身份证信息sql语句：

SELECT * FROM
(SELECT 姓名, 身份证号,
Right(身份证号,1) AS 身份证校验码,
cast(sum(cast(substring(身份证号,1,1) as int)*7
+cast(substring(身份证号,2,1) as int)*9
+cast(substring(身份证号,3,1) as int)*10
+cast(substring(身份证号,4,1) as int)*5
+cast(substring(身份证号,5,1) as int)*8
+cast(substring(身份证号,6,1) as int)*4
+cast(substring(身份证号,7,1) as int)*2
+cast(substring(身份证号,8,1) as int)*1
+cast(substring(身份证号,9,1) as int)*6
+cast(substring(身份证号,10,1) as int)*3
+cast(substring(身份证号,11,1) as int)*7
+cast(substring(身份证号,12,1) as int)*9
+cast(substring(身份证号,13,1)as int)*10
+cast(substring(身份证号,14,1) as int)*5
+cast(substring(身份证号,15,1)as int)*8
+cast(substring(身份证号,16,1) as int)*4
+cast(substring(身份证号,17,1) as int)*2) % 11 as varchar) as 校验码
,b.对应校验码
from [学生名单1$] a LEFT JOIN [身份证校验对照表$] b ON Right(a.身份证号,1) = b.身份证校验码 WHERE Len(身份证号)=18
GROUP BY 姓名,
身份证号,
Right(a.身份证号,1),
b.对应校验码
) c
where c.校验码<>c.对应校验码

执行错误提示：
消息 16，级别 8114，状态 5，第 1 行: 从数据类型 nvarchar 转换为 float 时出错。

Answer 1

你先把身份证号转成varchar再并腔亮进行绝宽字符串处理，把所有的 身份证号 改圆州成 convert(varchar(50),身份证号)试试

追问

用语句如何实现？新手，详细点哦！

Answer 2

说明查出来的数据不是数字之类的