1
--
处理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 = @rea
7 select @reation = dbo.f_getinfo( @rea )
8
9 if 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
14 if 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
28 select @reation as ' 藉贯 ' , @birthday as ' 出生日期 ' , @sexs as ' 性别 ' , @post as ' 邮政编码 '
29 GO
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 = @rea
7 select @reation = dbo.f_getinfo( @rea )
8
9 if 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
14 if 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
28 select @reation as ' 藉贯 ' , @birthday as ' 出生日期 ' , @sexs as ' 性别 ' , @post as ' 邮政编码 '
29 GO