提取身份证信息的自定义函数

 1 SET QUOTED_IDENTIFIER ON 
 2  GO
 3 SET ANSI_NULLS ON 
 4 GO
 5 
 6 ALTER  Function Nxl_DriverBirthAndSex(@IdCard VarChar(18))
 7 Returns VarChar(13) --提取身份证信息
 8 AS
 9 BEGIN
10 Declare @BirthDay Varchar(10),@Sex Varchar(2)
11 Set @IdCard=rtrim(ltrim(@IdCard))
12 If (len(@IdCard)=18)
13   Begin 
14     set @BirthDay=substring(@IdCard,7,4)+'-'+substring(@IdCard,11,2)+'-'+substring(@IdCard,13,2) 
15     if substring(@IdCard,17,1) in (2,4,6,8,0) Set @Sex='女' Else Set @Sex='男'
16   End
17   else if (len(@IdCard)=15)
18   Begin 
19     If Convert(Int,substring(@IdCard,7,2))<10
20       Set @BirthDay='20'+substring(@IdCard,7,2)+'-'+substring(@IdCard,9,2)+'-'+substring(@IdCard,11,2)
21     Else
22       Set @BirthDay='19'+substring(@IdCard,7,2)+'-'+substring(@IdCard,9,2)+'-'+substring(@IdCard,11,2)
23     if substring(@IdCard,15,1) in (2,4,6,8,0) Set @Sex='女' Else Set @Sex='男'
24   End
25   Return @BirthDay+'_'+@Sex
26 END
27 
28 GO
29 SET QUOTED_IDENTIFIER OFF 
30 GO
31 SET ANSI_NULLS ON 
32 GO
33 
34