1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author:<Author,,Name >
7 -- Create date: <Create Date, , >
8 -- Description: <Description, , >
9 -- =============================================
10 ALTER FUNCTION [dbo].[fn_IsValidIDCard]
11 (
12 @IDCardNo varchar(50)=''
13 )
14 RETURNS bit
15 AS
16 /*******************************************************************
17 函数名称：fn_IsvalidIDCard()
18 参数:@IDCardNo string 身份证号码
19 返回值： bit 是否有效
20 功能描述：判断身份证号码是否合法
21
22 备注：目前中国的身份证号码有18位和15位.
23 1，18位身份证号码的组成:6位地区编码＋8位出生年月日＋3位编号（奇男偶女）＋1位校验码
24 2，15位身份证号码的组成:6位地区编码＋6位出生年月日＋3位编号（奇男偶女）
25 *******************************************************************/
26 BEGIN
27
28 declare @Length int,
29 @Loop int,
30 @Sum int
31 declare @SingleChar char
32
33 set @Sum = 0
34 if @IDCardNo is null or @IDCardNo = null or ltrim(rtrim(@IDCardNo)) = ''
35 begin
36 return 0
37 end
38
39 set @Length = len(@IDCardNo)
40 --判断位数
41 if @Length < > 18 and @Length < > 15
42 begin
43 return 0
44 end
45
46 if @Length = 18
47 begin
48 if isnumeric(left(@IDCardNo, 17)) = 0
49 begin
50 return 0
51 end
52 if isdate(substring(@IDCardNo, 7, 4) + '-' + substring(@IDCardNo, 11, 2) + '-' + substring(@IDCardNo, 13, 2)) = 0
53 begin
54 return 0
55 end
56 set @Loop = 17
57 while (@Loop >= 1)
58 begin
59 set @Sum = @Sum + convert(int,substring(@IDCardNo, @Loop, 1)) * (power(2,(18 - @Loop)) % 11)
60 set @Loop = @Loop - 1
61 end
62 set @Loop = @Sum % 11
63 if @Loop = 0
64 begin
65 set @SingleChar = '1'
66 end
67 else if @Loop = 1
68 begin
69 set @SingleChar = '0'
70 end
71 else if @Loop = 2
72 begin
73 set @SingleChar = 'X'
74 end
75 else
76 begin
77 set @SingleChar = convert(varchar(2),(12 - @Loop))
78 end
79 if lower(Right(@IDCardNo, 1)) < > lower(@SingleChar)
80 begin
81 return 0
82 end
83 end
84 else if @Length = 15
85 begin
86 if isnumeric(@IDCardNo) = 0
87 begin
88 return 0
89 end
90 if isdate('19' + substring(@IDCardNo, 7, 2) + '-' + substring(@IDCardNo, 9, 2) + '-' + substring(@IDCardNo, 11, 2)) = 0
91 begin
92 return 0
93 end
94 end
95
96 return 1
97
98 END
