SQLServer中获取18位身份证号码校验码的函数

第一篇：SQLServer中获取18位身份证号码校验码的函数

在SQL Server中编写获取18位身份证的校验码函数

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fn_getChkVal](@IDCardNo nvarchar(18))RETURNS nvarchar(1)AS BEGIN Declare @iSum integer,@iMod integer if len(@IDCardNo)<>18 return '' if LEFT(@IDCardNo,17)like '%[^0-9]%' RETURN ''--判断1-17位是否为数字0-9，如果不是退出

set @iSum= cast(substring(@IDCardNo,1,1)as int)*7 set @iSum=@iSum+cast(substring(@IDCardNo,2,1)as int)*9 set @iSum=@iSum+cast(substring(@IDCardNo,3,1)as int)*10 set @iSum=@iSum+cast(substring(@IDCardNo,4,1)as int)*5 set @iSum=@iSum+cast(substring(@IDCardNo,5,1)as int)*8 set @iSum=@iSum+cast(substring(@IDCardNo,6,1)as int)*4 set @iSum=@iSum+cast(substring(@IDCardNo,7,1)as int)*2 set @iSum=@iSum+cast(substring(@IDCardNo,8,1)as int)*1 set @iSum=@iSum+cast(substring(@IDCardNo,9,1)as int)*6 set @iSum=@iSum+cast(substring(@IDCardNo,10,1)as int)*3 set @iSum=@iSum+cast(substring(@IDCardNo,11,1)as int)*7 set @iSum=@iSum+cast(substring(@IDCardNo,12,1)as int)*9 set @iSum=@iSum+cast(substring(@IDCardNo,13,1)as int)*10 set @iSum=@iSum+cast(substring(@IDCardNo,14,1)as int)*5 set @iSum=@iSum+cast(substring(@IDCardNo,15,1)as int)*8 set @iSum=@iSum+cast(substring(@IDCardNo,16,1)as int)*4 set @iSum=@iSum+cast(substring(@IDCardNo,17,1)as int)*2

set @iMod=@iSum % 11

return substring('10X98765432',@imod+1,1)END

第二篇：身份证号码函数

用vlookup函数，及其他各项功能，通过身份证号得出了出生日期、性别、户籍所在地等多项信息，并加入了身份证号正确性验证机制和15位、18位号码的转换功能。

详细制作步骤

要想了解身份证的秘密，首先让我们了解一下我国现行的公民身份证标准。1.我国现行使用公民身份证号码有两种标准。2.15位身份证号码（六位数字地址码，六位数字出生日期码，三位数字顺序码）。

3.18位身份证号码（六位数字地址码，八位数字出生日期码，三位数字顺序码和

一位数字校验码）。

4.5.地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。

6.日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。

7.顺序码表示同一范围内对同年、月、日出生的人员编定的顺序号。奇数分给男性，偶数分给女性。

8.校验码是根据前面十七位数字码计算出来的检验码。

复制代码

了解了以上知识后，让我们来制作Excel表。

1.新建一个Excel文档，2.将Sheet1重命名为“身份证信息”，在第1行各列中依次输入“身份证号码、十五位身份证号码、十八位身份证号码、性别、出生日期、年龄、省份、市、区县”等文字，并将“出生日期”列设置为日期（yyyy.mm.dd）格式，其余列

设置为文本格式。

3.将Sheet2重命名为“区域信息”，从国家统计局下载到最新县及县以上行政

区划代码，经整理后分别导入到“区域信息”的A、B两列中，以便查询。

复制代码

到此，Excel表框架搭建完成，我们来写入函数完成查询。

第一步 判断号码是否为正确的身份证号

一个正确的身份证号码，要符合以下几个标准：

①应为15位或18位；

②要包含数字（0－9）或字符（X）；

③18位身份证号最后一位是真正的校验码。

如果上面三个条件都满足，则是正确的身份证号。

我们利用Excel单元格的“有效性”限制来完成校验。

具体方法是选择“身份证信息”表，全选“身份证号码”列，选择“数据－有效性”菜单命令，在弹出的窗口“设置”标签中，将有效性条件的“允许”设置为“自定义”、“公式”

设置为

＝

OR(LEN(A2)=15,IF(LEN(A2)=18,MID(“10X98765432”,MOD(SUM(MID(A2,ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1)=RIGHT(A2)))，如图一。

在“出错警告”标签中，设置出错提示信息，如图二。

第二步 15位和18位身份证号码的互换

具体方法是选择“身份证信息”表，在B2单元格中输入

=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))；

在C2单元格中输入

{=IF(LEN(A3)=15,REPLACE(A3,7，19)&MID(“10X98765432”,MOD(SUM(MID(REPLACE(A3,7，19),ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1),A3)} 这里要注意是数组！

第三步 判断性别

如果是15位的，则取最后一位；

如果是18位的号，则取倒数第二位；

最后判断该数值的奇偶性即可。

具体方法是选择“身份证信息”表，在D2单元格中输入

=IF(A2<>“",IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)

第四步 判断出生日期

如果是15位的，则取第7至12位，在年份数前加上“19”；

如果是18位的，则直接取第7至14位；

具体方法是选择“身份证信息”表，在E2单元格中输入

=IF(A2<>”“,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)

第五步 判断年龄

这里利用一个Excel的隐藏函数 DATEDIF（）

基本语法： =DATEDIF(开始日期，结束日期，单位代码），用“Y”来表示年份。具体方法是选择“身份证信息”表，在F2单元格中输入

=IF(A2<>”“,DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”Y“),)

第六步 判断籍贯，包括省份、市、区县

这是本期比赛最难的一部分，关键在于如何多重判断省份、市、区县信息。

首先要知道地址码的构成。代码前两位是省或直辖市代码，中间两位是市代码，最后两位是区县代码。

其次是数据存放。“区域信息”表中已经存放了查询所需的数据，将数据区命名为“Code”以便查询。如图三

最后利用VLOOKUP函数来查询数据。

基本语法：＝VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

具体方法是选择“身份证信息”表，在G2单元格中输入=IF(A2<>”“,VLOOKUP(LEFT(A2,2)&”0000“,code,2,),)

在G2单元格中输入=IF(A2<>”“,VLOOKUP(LEFT(A2,4)&”00“,code,2,),)

在G2单元格中输入=IF(A2<>”",VLOOKUP(LEFT(A2,6),code,2,),)

最后一步 根据需要拖拽

刚才所写的公式只是在第2行写入的，大家可以根据需要向下拖拽，具体方法略去。

第三篇：身份证信息获取函数

、根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,“女”,“男”),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,“女”,“男”),“身份证错”))

2、根据身份证号码求出生年月：=IF(LEN(B2)=15,CONCATENATE(“19”,MID(B2,7,2),“.”,MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),“.”,MID(B2,11,2)),“身份证错”))

3、根据身份证号码求年龄：=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),“身份证错”))

一、分析身份证号码

其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。

15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。

18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。

例如，某员工的身份证号码(15位)是***，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。

二、提取个人信息

这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。

1.提取出生年月信息

由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：

LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。

MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。

=MID(F2,7,4)&“-”&MID(F2,11,2)&“-”&MID(F2,13,2)

MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。

IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。

如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。

2.提取性别信息

由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错例如性别信息统一在B列填写，可以在B2单元格中输入公式

“=IF(MOD(IF(LEN(F2)=15,MID(F2,15,1),MID(F2,17,1)),2)=1,“男”,“女”)”，其中：

LEN(C2)=15：检查身份证号码的长度是否是15位。

MID(C2,15,1)：如果身份证号码的长度是15位，那么提取第15位的数字。

MID(C2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。

MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。

IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。

第四篇：网上服务平台“身份证号码升位”操作指南范文

牛哞哞博客关注西安市雁塔区社会保险服务办事指南

网上服务平台“身份证号码升位”操作指南

一、总体操作流程

首先，通过“人员管理”模块下“在职人员身份证号升位”功能和“离退休人员身份证号升位”功能分别对本单位在职、退休人员的15位身份证号进行升位。其次，升位成功后，通过“数据交互”模块下“数据交互”功能进行数据上报。最后，携带相关资料到经办机构进行审核。

二、在职人员身份号码升位

第一步，点击“在职人员身份证号升位”功能。牛哞哞博客关注西安市雁塔区社会保险服务办事指南

第二步，选择导盘类型为“导出”，点击“查询”按钮。这里会将本单位下所有在职身份证号为15位的人员列出，并按照公安部门规则对其分别拟生成18位身份证号信息。

第三步，点击“生成”按钮。牛哞哞博客关注西安市雁塔区社会保险服务办事指南

这里有两个列表，“身份证号码升位成功信息”列表展示的是所有15位身份证号可以按照公安部门规则生成18位身份证号的信息内容。“身份证号码升位失败信息”列表展示的是不能按照公安部门规则生成18位身份证号的信息内容。第四步，导出升级成功信息。

牛哞哞博客关注西安市雁塔区社会保险服务办事指南

导出内容为excel表格，将其保存下来。第五步，对导出的excel表格进行编辑。

表格中将本单位户下身份证号为15位人员以及对应的拟升位信息列示出来。大家需要完成工作有：

1、将拟生成身份证号升位信息逐一进行核对。

2、保留核对正确的人员信息，删除核对不正确人员信息。

3、全部核对完成后，将修改后的文件予以保存。

第六步，点击“在职人员身份证号升位”功能，将导盘类型选择为“导入”。牛哞哞博客关注西安市雁塔区社会保险服务办事指南

在导入文件处，通过浏览选择已进行核对的excel表格，并点击“查询”按钮。

这里“身份证升位成功信息”列表展示就是本单位经核对后保留的拟升位信息。点击“确定”按钮，导入成功。第七步，点击“数据上报”功能。牛哞哞博客关注西安市雁塔区社会保险服务办事指南

“待上报业务信息汇总列表”列示的为拟上报信息，如果上报前发现报送内容有误，可通过

进行撤销。如果无误，点击“数据上报”按钮，并携带相关资料到经办机构进行审核。

三、离退休人员身份号码升位

通过“离退休人员身份号码升位”功能进行离退人员15位身份牛哞哞博客关注西安市雁塔区社会保险服务办事指南

证号升18位，具体操作，同“在职人员身份号码升位”操作步骤。

四、注意事项

1、导出的升级成功excel表格，仅允许进行对正确的保留操作，对不正确的删除对应行操作，不允许进行身份证号等信息的变更操作。

2、对于核实出不正确的升位信息，在过原渠道办理变更手续。

excel表格删除后，需通

第五篇：从身份证号码中抠出个人信息

从身份证号码中“抠出”个人信息

贵州省余庆中学杨松

最近，学校要求对全校教师的个人资料进行收集，其中包含：姓名，性别，年龄，出生日期，身份证号码„„档案室的老师这两天在加班加点的忙碌，一项一项的输入。我看在眼里急在心里，要是能直接把身份证号码中的出生日期和性别提取出来那多好啊。这样既可避免出错又能减轻多少负担啊！

统计表的格式如图：

经过分析，反复实践操作，终于找到了解决办法。

一、身份证号的组成我国公民身份号码是由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码（15位的身份证号码中出生日期码为6位，年份中省去了19两数，同时也无数字校验码）。第17位代表性别，奇数为男，偶数为女（15位身份证中的第15位代表性别）。

如某老师的身份证号码(18位)是***013，那么表示1968年12月10日出生，性别为男。

二、解决思路

１、分别将年、月、日及性别位的数字从身份证号码中提取出来；

２、运用公式将出生日期合并成指定的格式；

３、计算年龄，判断性别。

三、实施步骤

主要用到EXCEL中的函数MID、IF、LEN、CONCATENATE、MOD。MID：提取指定位置的字符串

IF：逻辑判断，结果为真或假

LEN：计算指定字符串的长度

CONCATENATE：将多个字符串合并成一个字符串

MOD：两数相除取余数。

（一）先将表格设计成如图所示的样式

（二）取年份

在D2单元格中输入公式

“=IF(LEN(C4)=18,MID(C4,7,4),CONCATENATE(“19”,MID(C4,7,2)))，其中：

LEN(C2)=18：检查C2单元格中字符串的字符数，本例的含义是检查

身份证号码的长度是否是18位。

MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取18位身份证号码的第7、8、9、10位数字。

MID(C2,7,2)：从C2单元格中字符串的第7位开始提取两位数字，本例中表示提取15位身份证号码的第7、8位数字。

CONCATENATE(“19”,MID(C4,7,2))：用字符“１９”与提取的字符进行合并，本例表示在提取的两位年份前加上“19”使其变为４位数的年份。例：使７８变成１９７８。

=IF(LEN(C4)=18,MID(C4,7,4),CONCATENATE(“19”,MID(C4,7,2)))：IF是一个逻辑判断函数，表示如果C2单元格是18位，则提取第7位开始的四位数字，否则提取自第7位开始的两位数字，并在前面加上“19”使其变为正常的４位数字的年份。即取得年份。

（三）取月、日和性别位的值

同理在E2、F2、G2中输入对应公式

在E2单元格中输入公式“=IF(LEN(C2)=18,MID(C2,11,2),MID(C2,9,2))”

判断身份证号码是１８位还是１５位。１８位身份证号码的第11、1

2位为月份，１５位的第9、10位为月份。

――取得月份

在F2单元格中输入公式“=IF(LEN(C2)=18,MID(C2,13,2),MID(C2,11,2))”

――取得日

在G2单元格中输入公式“=IF(LEN(C2)=18,MID(C2,17,1),MID(C2,15,1))”

――取得性别位的数值

（四）将年月日合并成指定的日期格式

在H2单元格中输入公式“=CONCATENATE(D2,“-”,E2,“-”,F2)”。

该公式表示将多个字符串合并成一个字符串。本例是将单元格D2、E2、F2中的内容按指定格式（yyyy-mm-dd）合并成一个字符串（若日期格式为“XXXX

年XX月XX日”，只需将公式改为“=CONCATENATE(D2,“年”,E2,“月”,F2,”日”)”即可）。

（五）判断性别

在I2单元格中输入公式“=IF(MOD(G2,2)=1,“男”,“女”)”

其中：

MOD(G2,2)＝1：用G2单元格的内容与2相除取余数，本例是判断性别位上的数是奇数还是偶数（余数是１还是０）。

=IF(MOD(G2,2)=1,“男”,“女”)：IF是一个逻辑判断函数，表示如果余数是“１”，则显示为“男”，否则显示为“女”。

（六）计算年龄

在J2单元格中输入“=2006-D2”

表示用2006年减去出生年份就可得出实际年龄了。

经过以上几步的设置之后，便可得如图所示的结果。

再用填充柄工具填充数据即可。如图：

四、一步到位法。

上面的方法简单，容易理解，适合初学者学习使用，但这种方法在实际操作中会产生几列不需要的数据（如本例中的年、月、日、性别位的值等列）。领会了思路之后完全可以一步到位将出生日期、性别和年龄算出来，而不需要用其它单元格转换。可以首先用上面的方法把公式设置好，然后再用替换法直接把中转的单元格用公式代替就可以了。只是在替换的时候要小心仔细，不得马虎。

按要求输入的格式如图：

分别在C2、D2、E2单元格中输入对应公式。

在C2单元格中输入公式

=IF(MOD(IF(LEN(F2)=18,MID(F2,17,1),MID(F2,15,1)),2)=1,“男”,“女”)

实际是将原公式“=IF(MOD(G2,2)=1,“男”,“女”)”中的G2直接用IF(LEN(F2)=18,MID(F2,17,1),MID(F2,15,1))替换。

同理在D2单元格中输入公式

=2006-IF(LEN(F2)=18,MID(F2,7,4),CONCATENATE(“19”,MID(F2,7,2)))

――直接用公式替换原D

2在E2单元格中输入公式

=CONCATENATE(IF(LEN(F2)=18,MID(F2,7,4),CONCATENATE(“19”,MID(F2,7,2))),“-”,IF(LEN(F2)=18,MID(F2,11,2),MID(F2,9,2)),“-”,IF(LEN(F2)=18,MID(F2,13,2),MID(F2,11,2)))

――直接用公式分别替换原D2、E2、F

2得到如下图所示的结果：

最后用填充柄填充数据即可。如图。

五、总结

Excel中的公式编辑功能非常强大，熟练掌握公式，巧妙运用公式往往能

使工作效率大为提高。

＝＝作者地址：贵州省余庆县余庆中学电教中心杨松 564400＝＝ ＝＝联系方式：ＱＱ：23194864 TEL：***＝＝ ＝＝邮箱：ysonion@163.com＝＝