本文介绍了一个SQL脚本，用于解析15位和18位的身份证号码，从中提取出生日期和性别信息，并通过查询CID_info表获取对应的邮政编码和籍贯。此脚本适用于数据库环境中身份证信息的快速处理。
1
--处理18位,15位的身份证号码从号码中得到生日和性别代码,邮政编码,籍贯
2DECLARE @id varchar(30),@rea varchar(30),@reation varchar(30),@birthday varchar(30),@sex varchar(10),@sexs varchar(2),@post varchar(10)
3set @id='362425197702141217'
4--set @id='362425770214121' --15位测试
5set @rea=substring(@id,1,6)
6select @post=PostCode from Cid_info where CID=@rea
7select @reation=dbo.f_getinfo(@rea)
8
9if len(@id)=18
10 begin
11 set @birthday=substring(@id,7,4)+'-'+substring(@id,11,2)+'-'+substring(@id,13,2)
12 set @sex=substring(@id,15,3)
13 end
14if len(@id)=15
15 begin
16 set @birthday='19'+substring(@id,7,2)+'-'+substring(@id,9,2)+'-'+substring(@id,11,2)
17 set @sex=substring(@id,13,3)
18 end
19 set @sex=cast(@sex as int)
20 if @sex%2=0
21 begin
22 Set @sexs='女'
23 end
24 else
25 begin
26 set @sexs='男'
27 end
28select @reation as '藉贯',@birthday as '出生日期', @sexs as '性别',@post as '邮政编码'
29GO
--处理18位,15位的身份证号码从号码中得到生日和性别代码,邮政编码,籍贯2
DECLARE @id varchar(30),@rea varchar(30),@reation varchar(30),@birthday varchar(30),@sex varchar(10),@sexs varchar(2),@post varchar(10)3
set @id='362425197702141217'4
--set @id='362425770214121' --15位测试5
set @rea=substring(@id,1,6)6
select @post=PostCode from Cid_info where CID=@rea7
select @reation=dbo.f_getinfo(@rea)8
9
if len(@id)=1810
begin11
set @birthday=substring(@id,7,4)+'-'+substring(@id,11,2)+'-'+substring(@id,13,2)12
set @sex=substring(@id,15,3)13
end14
if len(@id)=1515
begin16
set @birthday='19'+substring(@id,7,2)+'-'+substring(@id,9,2)+'-'+substring(@id,11,2)17
set @sex=substring(@id,13,3)18
end19
set @sex=cast(@sex as int)20
if @sex%2=021
begin22
Set @sexs='女'23
end24
else25
begin26
set @sexs='男'27
end28
select @reation as '藉贯',@birthday as '出生日期', @sexs as '性别',@post as '邮政编码'29
GO
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
