安徽淮北：一个单元格中有多个身份证号的拆分方法

发布日期：2020-12-30 08:50 编辑：张莹莹 来源：市审计局 阅读： 次 字号： 大 中 小

在审计过程中，采集相关业务的电子数据，经常会遇到多个人的身份证号在同一单元格内的情况，身份证号需要清理放在不同的单元格后再与相关外部数据进行比对。以保障性安居工程审计项目为例，符合条件的保障对象申请住房租赁补贴或公租房租赁等住房保障时以家庭为单位，所以采集的相关电子数据中存在多名家庭成员身份证号列在同一个单元格中的情况。

1.清理身份证号相关字段的空格

全市租赁补贴数据表中申请人身份证号和家庭成员身份证号中都有空格，去除证件号码相关字段的空格，并提取申请人，申请人证件号码，家庭成员证件号码三列形成中间表，生成新表：zjb_全市廉补证件号。

Sql语句：

select 申请人, replace(证件号码,' ','') as 身份证号,replace(家庭成员证件号码,' ','') as zjh into zjb_全市廉补证件号 from dbo.全市租赁补贴数据$

2.计算家庭成员的人数，判读需插入多少列家庭成员身份证号

Sql语句：

select  distinct LEN(zjh) from zjb_全市廉补证件号 order by LEN(zjh)

执行结果显示家庭成员身份证号最大126位，判断家庭成员最多7人。插入7列家庭成员身份证号

alter table zjb_全市廉补证件号 add 家庭成员身份证号1 nvarchar (255)

alter table  zjb_全市廉补证件号 add 家庭成员身份证号2 nvarchar (255)

alter table  zjb_全市廉补证件号 add 家庭成员身份证号3 nvarchar (255)

alter table  zjb_全市廉补证件号 add 家庭成员身份证号4 nvarchar (255)

alter table  zjb_全市廉补证件号 add 家庭成员身份证号5 nvarchar (255)

alter table  zjb_全市廉补证件号 add 家庭成员身份证号6 nvarchar (255)

alter table  zjb_全市廉补证件号 add 家庭成员身份证号7 nvarchar (255)

3.把家庭成员身份证号分别填入对应的列

Sql语句：

update zjb_全市廉补证件号 set 家庭成员身份证号1=case when LEN(zjh)=18 then zjh

when LEN(zjh)>18 then SUBSTRING(zjh,1,18) end

update zjb_全市廉补证件号 set 家庭成员身份证号2=case when LEN(zjh)=36 then SUBSTRING(zjh,19,18)

when LEN(zjh)>36 then SUBSTRING(zjh,19,18) end

update zjb_全市廉补证件号 set 家庭成员身份证号3=case when LEN(zjh)=54 then SUBSTRING(zjh,37,18)

when LEN(zjh)>54 then SUBSTRING(zjh,37,18)end

update zjb_全市廉补证件号 set 家庭成员身份证号4=case when LEN(zjh)=72 then SUBSTRING(zjh,55,18)

when LEN(zjh)>72 then SUBSTRING(zjh,55,18) end

update zjb_全市廉补证件号 set 家庭成员身份证号5=case when LEN(zjh)=90 then SUBSTRING(zjh,73,18)

when LEN(zjh)>90 then SUBSTRING(zjh,73,18) end

update zjb_全市廉补证件号 set 家庭成员身份证号6=case when LEN(zjh)=108 then SUBSTRING(zjh,91,18)

when LEN(zjh)>108 then SUBSTRING(zjh,91,18) end

update zjb_全市廉补证件号 set 家庭成员身份证号7=case when LEN(zjh)=126 thenSUBSTRING(zjh,109,18)

when LEN(zjh)>126 then SUBSTRING(zjh,109,18)   end

4.处理后的表格与全市租赁补贴数据链接，形成租赁补贴数据。

Sql语句：

select a.申请人,a.证件号码,b.家庭成员身份证号1,b.家庭成员身份证号2,b.家庭成员身份证号3,b.家庭成员身份证号4,b.家庭成员身份证号5,b.家庭成员身份证号6,b.家庭成员身份证号7,a.保障人口,a.申请人类型,a.所属区县 into 全市租赁补贴处理后数据1

from dbo.全市租赁补贴数据$ a  join zjb_全市廉补证件号b on a.证件号码=b.证件号码

通过sql语句拆分，保障对象的家庭成员身份证号放在了不同的单元格，通过每个保障对象的身份证号与相关外部数据进行比对，可以筛查出违规享受保障住房的人员信息。

返回顶部