sql server中使用函数验证身份证号码是否合法 

 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

 

posted @ 2021-12-08 15:47  每天进步多一点  阅读(1450)  评论(0)    收藏  举报