写了个15位身份证号升级为18位的SQL语句

最新推荐文章于 2023-11-28 20:27:23 发布

游戏人生

最新推荐文章于 2023-11-28 20:27:23 发布

阅读量2.2k

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/tielu0144/article/details/1929650

版权

-- 15位身份证号升级为18位，适用于18xx年出生的公民
UPDATE 员工表 SET 身份证号 =
SUBSTRING (身份证号, 1 , 6 ) + ' 18 ' + SUBSTRING (身份证号, 7 , 9 ) +
SUBSTRING ( ' 10X98765432 ' ,
(
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
+ 1 * 2
+ 8 * 1
+ CAST ( SUBSTRING (身份证号, 7 , 1 ) AS INT ) * 6
+ CAST ( SUBSTRING (身份证号, 8 , 1 ) AS INT ) * 3
+ CAST ( SUBSTRING (身份证号, 9 , 1 ) AS INT ) * 7
+ CAST ( SUBSTRING (身份证号, 10 , 1 ) AS INT ) * 9
+ CAST ( SUBSTRING (身份证号, 11 , 1 ) AS INT ) * 10
+ CAST ( SUBSTRING (身份证号, 12 , 1 ) AS INT ) * 5
+ CAST ( SUBSTRING (身份证号, 13 , 1 ) AS INT ) * 8
+ CAST ( SUBSTRING (身份证号, 14 , 1 ) AS INT ) * 4
+ CAST ( SUBSTRING (身份证号, 15 , 1 ) AS INT ) * 2
)
% 11 + 1 , 1 )
WHERE LEN (身份证号) = 15 AND SUBSTRING (身份证号, 13 , 3 ) IN ( ' 999 ' , ' 998 ' , ' 997 ' , ' 996 ' )

-- 15位身份证号升级为18位，适用于19xx年出生的公民
UPDATE 员工表 SET 身份证号 =
SUBSTRING (身份证号, 1 , 6 ) + ' 19 ' + SUBSTRING (身份证号, 7 , 9 ) +
SUBSTRING ( ' 10X98765432 ' ,
(
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
+ 1 * 2
+ 9 * 1
+ CAST ( SUBSTRING (身份证号, 7 , 1 ) AS INT ) * 6
+ CAST ( SUBSTRING (身份证号, 8 , 1 ) AS INT ) * 3
+ CAST ( SUBSTRING (身份证号, 9 , 1 ) AS INT ) * 7
+ CAST ( SUBSTRING (身份证号, 10 , 1 ) AS INT ) * 9
+ CAST ( SUBSTRING (身份证号, 11 , 1 ) AS INT ) * 10
+ CAST ( SUBSTRING (身份证号, 12 , 1 ) AS INT ) * 5
+ CAST ( SUBSTRING (身份证号, 13 , 1 ) AS INT ) * 8
+ CAST ( SUBSTRING (身份证号, 14 , 1 ) AS INT ) * 4
+ CAST ( SUBSTRING (身份证号, 15 , 1 ) AS INT ) * 2
)
% 11 + 1 , 1 )
WHERE LEN (身份证号) = 15 AND SUBSTRING (身份证号, 13 , 3 ) NOT IN ( ' 999 ' , ' 998 ' , ' 997 ' , ' 996 ' )