检核SQL检核社会统一信用代码是否有效

最新推荐文章于 2024-06-12 17:05:15 发布
mocun～
最新推荐文章于 2024-06-12 17:05:15 发布
阅读量841
点赞数
文章标签： sql 数据库
本文链接：https://blog.csdn.net/mocun8/article/details/130825847
版权
该内容是针对社会统一信用代码进行的一系列有效性验证规则，包括检查代码位数、前两位区域代码、3-8位机构代码、9-17位数字校验以及最后一位校验码的计算。如果代码不符合这些规则，则会指出具体错误，否则视为校验通过。
摘要由CSDN通过智能技术生成
规则参考：
SELECT 
per.关联人证件类型,
per.关联人证件号,
/*检核社会统一信用代码有效性*/
(case 
/*检核社会统一信用代码是否为18位*/
when length(per.关联人证件号) <>  '18' then '社会统一信用代码位数有误'
/*判断前1-2位是否符合规范*/
when substr(per.关联人证件号,1,2) not in ('11','12','13','19','21','31','41','51','52','53','59','61','71','81','91','92','93','99','A1','B1','C1','D1','E1','F1','G1','Y1') THEN '社会统一信用代码1-2位有误'
/*判断前3-8位是否符合规范*/
when translate(substr(per.关联人证件号,3,6),'0123456789','') <> '' then '社会统一信用代码3-8位有误'
/*判断前9-18位是否符合规范*/
when translate(substr(per.关联人证件号,9,10),'0123456789ABCDEFGHJKLMNPQRTUWXY','') <> '' then '社会统一信用代码9-17位有误'
/*第18位校验码验证*/
when 31 - mod((
substr(per.关联人证件号,1,1)*1
+substr(per.关联人证件号,2,1)*3
+substr(per.关联人证件号,3,1)*9
+substr(per.关联人证件号,4,1)*27
+substr(per.关联人证件号,5,1)*19
+substr(per.关联人证件号,6,1)*26
+substr(per.关联人证件号,7,1)*16
+substr(per.关联人证件号,8,1)*17
+(case 
 when substr(per.关联人证件号,9,1) = 'A' then 10
 when substr(per.关联人证件号,9,1) = 'B' then 11
 when substr(per.关联人证件号,9,1) = 'C' then 12
 when substr(per.关联人证件号,9,1) = 'D' then 13
 when substr(per.关联人证件号,9,1) = 'E' then 14
 when substr(per.关联人证件号,9,1) = 'F' then 15
 when substr(per.关联人证件号,9,1) = 'G' then 16
 when substr(per.关联人证件号,9,1) = 'H' then 17
 when substr(per.关联人证件号,9,1) = 'J' then 18
 when substr(per.关联人证件号,9,1) = 'K' then 19
 when substr(per.关联人证件号,9,1) = 'L' then 20
 when substr(per.关联人证件号,9,1) = 'M' then 21
 when substr(per.关联人证件号,9,1) = 'N' then 22
 when substr(per.关联人证件号,9,1) = 'P' then 23
 when substr(per.关联人证件号,9,1) = 'Q' then 24
 when substr(per.关联人证件号,9,1) = 'R' then 25
 when substr(per.关联人证件号,9,1) = 'T' then 26
 when substr(per.关联人证件号,9,1) = 'U' then 27
 when substr(per.关联人证件号,9,1) = 'W' then 28
 when substr(per.关联人证件号,9,1) = 'X' then 29
 when substr(per.关联人证件号,9,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 20 
+(case 
 when substr(per.关联人证件号,10,1) = 'A' then 10
 when substr(per.关联人证件号,10,1) = 'B' then 11
 when substr(per.关联人证件号,10,1) = 'C' then 12
 when substr(per.关联人证件号,10,1) = 'D' then 13
 when substr(per.关联人证件号,10,1) = 'E' then 14
 when substr(per.关联人证件号,10,1) = 'F' then 15
 when substr(per.关联人证件号,10,1) = 'G' then 16
 when substr(per.关联人证件号,10,1) = 'H' then 17
 when substr(per.关联人证件号,10,1) = 'J' then 18
 when substr(per.关联人证件号,10,1) = 'K' then 19
 when substr(per.关联人证件号,10,1) = 'L' then 20
 when substr(per.关联人证件号,10,1) = 'M' then 21
 when substr(per.关联人证件号,10,1) = 'N' then 22
 when substr(per.关联人证件号,10,1) = 'P' then 23
 when substr(per.关联人证件号,10,1) = 'Q' then 24
 when substr(per.关联人证件号,10,1) = 'R' then 25
 when substr(per.关联人证件号,10,1) = 'T' then 26
 when substr(per.关联人证件号,10,1) = 'U' then 27
 when substr(per.关联人证件号,10,1) = 'W' then 28
 when substr(per.关联人证件号,10,1) = 'X' then 29
 when substr(per.关联人证件号,10,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 29 
+(case 
 when substr(per.关联人证件号,11,1) = 'A' then 10
 when substr(per.关联人证件号,11,1) = 'B' then 11
 when substr(per.关联人证件号,11,1) = 'C' then 12
 when substr(per.关联人证件号,11,1) = 'D' then 13
 when substr(per.关联人证件号,11,1) = 'E' then 14
 when substr(per.关联人证件号,11,1) = 'F' then 15
 when substr(per.关联人证件号,11,1) = 'G' then 16
 when substr(per.关联人证件号,11,1) = 'H' then 17
 when substr(per.关联人证件号,11,1) = 'J' then 18
 when substr(per.关联人证件号,11,1) = 'K' then 19
 when substr(per.关联人证件号,11,1) = 'L' then 20
 when substr(per.关联人证件号,11,1) = 'M' then 21
 when substr(per.关联人证件号,11,1) = 'N' then 22
 when substr(per.关联人证件号,11,1) = 'P' then 23
 when substr(per.关联人证件号,11,1) = 'Q' then 24
 when substr(per.关联人证件号,11,1) = 'R' then 25
 when substr(per.关联人证件号,11,1) = 'T' then 26
 when substr(per.关联人证件号,11,1) = 'U' then 27
 when substr(per.关联人证件号,11,1) = 'W' then 28
 when substr(per.关联人证件号,11,1) = 'X' then 29
 when substr(per.关联人证件号,11,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 25
  +(case 
 when substr(per.关联人证件号,12,1) = 'A' then 10
 when substr(per.关联人证件号,12,1) = 'B' then 11
 when substr(per.关联人证件号,12,1) = 'C' then 12
 when substr(per.关联人证件号,12,1) = 'D' then 13
 when substr(per.关联人证件号,12,1) = 'E' then 14
 when substr(per.关联人证件号,12,1) = 'F' then 15
 when substr(per.关联人证件号,12,1) = 'G' then 16
 when substr(per.关联人证件号,12,1) = 'H' then 17
 when substr(per.关联人证件号,12,1) = 'J' then 18
 when substr(per.关联人证件号,12,1) = 'K' then 19
 when substr(per.关联人证件号,12,1) = 'L' then 20
 when substr(per.关联人证件号,12,1) = 'M' then 21
 when substr(per.关联人证件号,12,1) = 'N' then 22
 when substr(per.关联人证件号,12,1) = 'P' then 23
 when substr(per.关联人证件号,12,1) = 'Q' then 24
 when substr(per.关联人证件号,12,1) = 'R' then 25
 when substr(per.关联人证件号,12,1) = 'T' then 26
 when substr(per.关联人证件号,12,1) = 'U' then 27
 when substr(per.关联人证件号,12,1) = 'W' then 28
 when substr(per.关联人证件号,12,1) = 'X' then 29
 when substr(per.关联人证件号,12,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 13 
  +(case 
 when substr(per.关联人证件号,13,1) = 'A' then 10
 when substr(per.关联人证件号,13,1) = 'B' then 11
 when substr(per.关联人证件号,13,1) = 'C' then 12
 when substr(per.关联人证件号,13,1) = 'D' then 13
 when substr(per.关联人证件号,13,1) = 'E' then 14
 when substr(per.关联人证件号,13,1) = 'F' then 15
 when substr(per.关联人证件号,13,1) = 'G' then 16
 when substr(per.关联人证件号,13,1) = 'H' then 17
 when substr(per.关联人证件号,13,1) = 'J' then 18
 when substr(per.关联人证件号,13,1) = 'K' then 19
 when substr(per.关联人证件号,13,1) = 'L' then 20
 when substr(per.关联人证件号,13,1) = 'M' then 21
 when substr(per.关联人证件号,13,1) = 'N' then 22
 when substr(per.关联人证件号,13,1) = 'P' then 23
 when substr(per.关联人证件号,13,1) = 'Q' then 24
 when substr(per.关联人证件号,13,1) = 'R' then 25
 when substr(per.关联人证件号,13,1) = 'T' then 26
 when substr(per.关联人证件号,13,1) = 'U' then 27
 when substr(per.关联人证件号,13,1) = 'W' then 28
 when substr(per.关联人证件号,13,1) = 'X' then 29
 when substr(per.关联人证件号,13,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 8 
  +(case 
 when substr(per.关联人证件号,14,1) = 'A' then 10
 when substr(per.关联人证件号,14,1) = 'B' then 11
 when substr(per.关联人证件号,14,1) = 'C' then 12
 when substr(per.关联人证件号,14,1) = 'D' then 13
 when substr(per.关联人证件号,14,1) = 'E' then 14
 when substr(per.关联人证件号,14,1) = 'F' then 15
 when substr(per.关联人证件号,14,1) = 'G' then 16
 when substr(per.关联人证件号,14,1) = 'H' then 17
 when substr(per.关联人证件号,14,1) = 'J' then 18
 when substr(per.关联人证件号,14,1) = 'K' then 19
 when substr(per.关联人证件号,14,1) = 'L' then 20
 when substr(per.关联人证件号,14,1) = 'M' then 21
 when substr(per.关联人证件号,14,1) = 'N' then 22
 when substr(per.关联人证件号,14,1) = 'P' then 23
 when substr(per.关联人证件号,14,1) = 'Q' then 24
 when substr(per.关联人证件号,14,1) = 'R' then 25
 when substr(per.关联人证件号,14,1) = 'T' then 26
 when substr(per.关联人证件号,14,1) = 'U' then 27
 when substr(per.关联人证件号,14,1) = 'W' then 28
 when substr(per.关联人证件号,14,1) = 'X' then 29
 when substr(per.关联人证件号,14,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 24
  +(case 
 when substr(per.关联人证件号,15,1) = 'A' then 10
 when substr(per.关联人证件号,15,1) = 'B' then 11
 when substr(per.关联人证件号,15,1) = 'C' then 12
 when substr(per.关联人证件号,15,1) = 'D' then 13
 when substr(per.关联人证件号,15,1) = 'E' then 14
 when substr(per.关联人证件号,15,1) = 'F' then 15
 when substr(per.关联人证件号,15,1) = 'G' then 16
 when substr(per.关联人证件号,15,1) = 'H' then 17
 when substr(per.关联人证件号,15,1) = 'J' then 18
 when substr(per.关联人证件号,15,1) = 'K' then 19
 when substr(per.关联人证件号,15,1) = 'L' then 20
 when substr(per.关联人证件号,15,1) = 'M' then 21
 when substr(per.关联人证件号,15,1) = 'N' then 22
 when substr(per.关联人证件号,15,1) = 'P' then 23
 when substr(per.关联人证件号,15,1) = 'Q' then 24
 when substr(per.关联人证件号,15,1) = 'R' then 25
 when substr(per.关联人证件号,15,1) = 'T' then 26
 when substr(per.关联人证件号,15,1) = 'U' then 27
 when substr(per.关联人证件号,15,1) = 'W' then 28
 when substr(per.关联人证件号,15,1) = 'X' then 29
 when substr(per.关联人证件号,15,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 10
  +(case 
 when substr(per.关联人证件号,16,1) = 'A' then 10
 when substr(per.关联人证件号,16,1) = 'B' then 11
 when substr(per.关联人证件号,16,1) = 'C' then 12
 when substr(per.关联人证件号,16,1) = 'D' then 13
 when substr(per.关联人证件号,16,1) = 'E' then 14
 when substr(per.关联人证件号,16,1) = 'F' then 15
 when substr(per.关联人证件号,16,1) = 'G' then 16
 when substr(per.关联人证件号,16,1) = 'H' then 17
 when substr(per.关联人证件号,16,1) = 'J' then 18
 when substr(per.关联人证件号,16,1) = 'K' then 19
 when substr(per.关联人证件号,16,1) = 'L' then 20
 when substr(per.关联人证件号,16,1) = 'M' then 21
 when substr(per.关联人证件号,16,1) = 'N' then 22
 when substr(per.关联人证件号,16,1) = 'P' then 23
 when substr(per.关联人证件号,16,1) = 'Q' then 24
 when substr(per.关联人证件号,16,1) = 'R' then 25
 when substr(per.关联人证件号,16,1) = 'T' then 26
 when substr(per.关联人证件号,16,1) = 'U' then 27
 when substr(per.关联人证件号,16,1) = 'W' then 28
 when substr(per.关联人证件号,16,1) = 'X' then 29
 when substr(per.关联人证件号,16,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 30
  +(case 
 when substr(per.关联人证件号,17,1) = 'A' then 10
 when substr(per.关联人证件号,17,1) = 'B' then 11
 when substr(per.关联人证件号,17,1) = 'C' then 12
 when substr(per.关联人证件号,17,1) = 'D' then 13
 when substr(per.关联人证件号,17,1) = 'E' then 14
 when substr(per.关联人证件号,17,1) = 'F' then 15
 when substr(per.关联人证件号,17,1) = 'G' then 16
 when substr(per.关联人证件号,17,1) = 'H' then 17
 when substr(per.关联人证件号,17,1) = 'J' then 18
 when substr(per.关联人证件号,17,1) = 'K' then 19
 when substr(per.关联人证件号,17,1) = 'L' then 20
 when substr(per.关联人证件号,17,1) = 'M' then 21
 when substr(per.关联人证件号,17,1) = 'N' then 22
 when substr(per.关联人证件号,17,1) = 'P' then 23
 when substr(per.关联人证件号,17,1) = 'Q' then 24
 when substr(per.关联人证件号,17,1) = 'R' then 25
 when substr(per.关联人证件号,17,1) = 'T' then 26
 when substr(per.关联人证件号,17,1) = 'U' then 27
 when substr(per.关联人证件号,17,1) = 'W' then 28
 when substr(per.关联人证件号,17,1) = 'X' then 29
 when substr(per.关联人证件号,17,1) = 'Y' then 30
 ELSE per.关联人证件号
 END
) * 28
),31
)  <>  (case 
 when substr(per.关联人证件号,18,1) = 'A' then 10
 when substr(per.关联人证件号,18,1) = 'B' then 11
 when substr(per.关联人证件号,18,1) = 'C' then 12
 when substr(per.关联人证件号,18,1) = 'D' then 13
 when substr(per.关联人证件号,18,1) = 'E' then 14
 when substr(per.关联人证件号,18,1) = 'F' then 15
 when substr(per.关联人证件号,18,1) = 'G' then 16
 when substr(per.关联人证件号,18,1) = 'H' then 17
 when substr(per.关联人证件号,18,1) = 'J' then 18
 when substr(per.关联人证件号,18,1) = 'K' then 19
 when substr(per.关联人证件号,18,1) = 'L' then 20
 when substr(per.关联人证件号,18,1) = 'M' then 21
 when substr(per.关联人证件号,18,1) = 'N' then 22
 when substr(per.关联人证件号,18,1) = 'P' then 23
 when substr(per.关联人证件号,18,1) = 'Q' then 24
 when substr(per.关联人证件号,18,1) = 'R' then 25
 when substr(per.关联人证件号,18,1) = 'T' then 26
 when substr(per.关联人证件号,18,1) = 'U' then 27
 when substr(per.关联人证件号,18,1) = 'W' then 28
 when substr(per.关联人证件号,18,1) = 'X' then 29
 when substr(per.关联人证件号,18,1) = 'Y' then 30
 ELSE substr(per.关联人证件号,18,1)
 END
) then '校验码有误' else '校验通过' end) as 社会统一信用代码校验
FROM 2023年数据质量考核客户基础信息_个人关联方明细数据 per