/****** 对象: UserDefinedFunction [Helper].[IDCard] 脚本日期: 12/13/2007 19:16:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [Helper].[IDCard] ( @Card varchar(18) ) RETURNS @TCard TABLE ( Input varchar(18) ,IDCard varchar(18) ,Valid bit ) AS BEGIN DECLARE @Input as varchar(18) ,@IDCard as varchar(18) ,@Valid as bit DECLARE @Length as smallint ,@TmpCard as varchar(18) ,@IsOld as bit SET @Valid = 0 SET @IDCard = '' SET @Input = '' IF @Card IS NULL GOTO Finish SET @Input = LTRIM(RTRIM(@Card)) /*去空格*/ SET @Length = LEN(@Input) IF NOT @Length IN (15, 18) GOTO Finish /*非15、18位*/ IF @Length = 15 BEGIN IF ISNUMERIC(@Input) = 0 GOTO Finish /*非数字*/ SET @TmpCard = LEFT(@Input, 6) + '19' + RIGHT(@input, 9) /*补充为17位*/ SET @IsOld = 1 END ELSE BEGIN IF ISNUMERIC(LEFT(@Input, 17)) = 0 GOTO Finish /*非数字*/ SET @TmpCard = LEFT(@Input, 17) /*取前17位*/ SET @IsOld = 0 END DECLARE @Birthday varchar(8) SET @Birthday = SUBSTRING(@TmpCard, 7, 8) IF ISDATE(@birthday) = 0 GOTO Finish /*非日期*/ --前17位数与相应加权因子积的和 DECLARE @Sum as smallint ,@WI as tinyint ,@Index as tinyint ,@Num as tinyint SET @Sum = 0 SET @Index = 1 WHILE @Index < 18 BEGIN SET @Num = CAST(SUBSTRING(@TmpCard, @Index, 1) AS tinyint) SELECT @WI = CASE @Index WHEN 1 THEN 7 WHEN 2 THEN 9 WHEN 3 THEN 10 WHEN 4 THEN 5 WHEN 5 THEN 8 WHEN 6 THEN 4 WHEN 7 THEN 2 WHEN 8 THEN 1 WHEN 9 THEN 6 WHEN 10 THEN 3 WHEN 11 THEN 7 WHEN 12 THEN 9 WHEN 13 THEN 10 WHEN 14 THEN 5 WHEN 15 THEN 8 WHEN 16 THEN 4 WHEN 17 THEN 2 END SET @Sum = @Sum + @Num * @WI SET @Index = @Index + 1 END --模11 DECLARE @Mod as tinyint SET @Mod = @Sum % 11 --校验码 DECLARE @Parity as varchar(1) SELECT @Parity = CASE @Mod WHEN 0 THEN '1' WHEN 1 THEN '0' WHEN 2 THEN 'X' WHEN 3 THEN '9' WHEN 4 THEN '8' WHEN 5 THEN '7' WHEN 6 THEN '6' WHEN 7 THEN '5' WHEN 8 THEN '4' WHEN 9 THEN '3' WHEN 10 THEN '2' END --完整的18位身份证号码 SET @TmpCard = @TmpCard + @Parity IF @IsOld = 1 SET @Valid = 1 ELSE IF @Parity = RIGHT(@Input, 1) /*校验*/ SET @Valid = 1 --无论对错,都给出有效身份证号码 SET @IDCard = @tmpCard Finish: INSERT INTO @TCard VALUES(@Input, @IDCard, @Valid) RETURN END
CREATE function GetCheckIDCardCode(@sfzh char(18)) returns char(1) as begin declare @r varchar(2) declare @i int if len(@sfzh) <> 18 set @r = 0 else set @i = cast(substring(@sfzh,1,1) as int) * 7 +cast(substring(@sfzh,2,1) as int) * 9 +cast(substring(@sfzh,3,1) as int) * 10 +cast(substring(@sfzh,4,1) as int) * 5 +cast(substring(@sfzh,5,1) as int) * 8 +cast(substring(@sfzh,6,1) as int) * 4 +cast(substring(@sfzh,7,1) as int) * 2 +cast(substring(@sfzh,8,1) as int) * 1 +cast(substring(@sfzh,9,1) as int) * 6 +cast(substring(@sfzh,10,1) as int) * 3 +cast(substring(@sfzh,11,1) as int) * 7 +cast(substring(@sfzh,12,1) as int) * 9 +cast(substring(@sfzh,13,1) as int) * 10 +cast(substring(@sfzh,14,1) as int) * 5 +cast(substring(@sfzh,15,1) as int) * 8 +cast(substring(@sfzh,16,1) as int) * 4 +cast(substring(@sfzh,17,1) as int) * 2 set @i = @i - @i/11 * 11 set @r = cast((case @i when 0 then 1 when 1 then 0 when 2 then 11 when 3 then 9 when 4 then 8 when 5 then 7 when 6 then 6 when 7 then 5 when 8 then 4 when 9 then 3 when 10 then 2 else '' end) as char)
if (@r = 11) set @r='X' else set @r = @r
set @r = '' + @r +'' return @r end
where dbo.GetCheckIDCardCode(身份证字段名)='X'
SQL简单验证身份证号码位数、出生日期、性别 主要验证SQL数据库中已输入的15位 及18位 身份证号码的位数、出生年月日是否正确,可以过滤出大部分的输入错误。or (len(身份证号)=主要验证SQL数据库中已输入的15位 及18位 身份证号码的位数、出生年月日是否正确 ,可以过滤出大部分的输入错误。or (len(身份证号)=18 and (Substring(身份证号,7,2)<‘19‘ or Substring(身份证号,7,2)>‘20‘or (Substring(身份证号,11,2)>12)or (Substring(身份证号,11,2) in (01,03,05,07,08,10,12) and Substring(身份证号,13,2)>31)or (Substring(身份证号,11,2) in (04,06,09,11) and Substring(身份证号,13,2)>30)or (Substring(身份证号,11,2)=02 and Substring(身份证号,13,2)>29)))下面是针对 15位 及18位 身份证号码性别的验证语句Access 不支持 Substring 查询,可以替换为 mid 查询。select 序号,姓名,身份证号,性别from 身份表where (((len(身份证号)=15) and (Substring(身份证号,15,1) in (1,3,5,7,9)) and 性别<>‘男‘)or ((len(身份证号)=15) and (Substring(身份证号,15,1) in (2,4,6,8,0)) and 性别<>‘女‘))or (((len(身份证号)=18) and (Substring(身份证号,17,1) in (1,3,5,7,9)) and 性别<>‘男‘)or ((len(身份证号)=18) and (Substring(身份证号,17,1) in (2,4,6,8,0)) and 性别<>‘女‘))注:转载请注明出处下面是针对 15位 及18位 身份证号码位数与出生年月日的验证Access 不支持 Substring 查询,可以替换为 mid 查询。select 序号,姓名,身份证号,性别from 身份表where (len(身份证号)<>15 and len(身份证号)<>18)or (len(身份证号)=15 and ((Substring(身份证号,9,2)>12)or (Substring(身份证号,11,2) > 31)or (Substring(身份证号,9,2) in (01,03,05,07,08,10,12) and Substring(身份证号,11,2)>31)or (Substring(身份证号,9,2) in (04,06,09,11) and Substring(身份证号,11,2)>30)or (Substring(身份证号,9,2)=02 and Substring(身份证号,11,2)>29)))
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Helper].[IDCard]
(
@Card varchar(18)
)
RETURNS
@TCard TABLE
(
Input varchar(18)
,IDCard varchar(18)
,Valid bit
)
AS
BEGIN
DECLARE
@Input as varchar(18)
,@IDCard as varchar(18)
,@Valid as bit
DECLARE
@Length as smallint
,@TmpCard as varchar(18)
,@IsOld as bit SET @Valid = 0
SET @IDCard = ''
SET @Input = '' IF @Card IS NULL GOTO Finish SET @Input = LTRIM(RTRIM(@Card)) /*去空格*/
SET @Length = LEN(@Input) IF NOT @Length IN (15, 18) GOTO Finish /*非15、18位*/ IF @Length = 15
BEGIN
IF ISNUMERIC(@Input) = 0 GOTO Finish /*非数字*/
SET @TmpCard = LEFT(@Input, 6) + '19' + RIGHT(@input, 9) /*补充为17位*/
SET @IsOld = 1
END
ELSE
BEGIN
IF ISNUMERIC(LEFT(@Input, 17)) = 0 GOTO Finish /*非数字*/
SET @TmpCard = LEFT(@Input, 17) /*取前17位*/
SET @IsOld = 0
END DECLARE @Birthday varchar(8)
SET @Birthday = SUBSTRING(@TmpCard, 7, 8)
IF ISDATE(@birthday) = 0 GOTO Finish /*非日期*/ --前17位数与相应加权因子积的和
DECLARE
@Sum as smallint
,@WI as tinyint
,@Index as tinyint
,@Num as tinyint SET @Sum = 0
SET @Index = 1 WHILE @Index < 18
BEGIN
SET @Num = CAST(SUBSTRING(@TmpCard, @Index, 1) AS tinyint)
SELECT @WI =
CASE @Index
WHEN 1 THEN 7
WHEN 2 THEN 9
WHEN 3 THEN 10
WHEN 4 THEN 5
WHEN 5 THEN 8
WHEN 6 THEN 4
WHEN 7 THEN 2 WHEN 8 THEN 1
WHEN 9 THEN 6
WHEN 10 THEN 3
WHEN 11 THEN 7
WHEN 12 THEN 9
WHEN 13 THEN 10
WHEN 14 THEN 5
WHEN 15 THEN 8
WHEN 16 THEN 4
WHEN 17 THEN 2
END SET @Sum = @Sum + @Num * @WI
SET @Index = @Index + 1
END
--模11
DECLARE @Mod as tinyint
SET @Mod = @Sum % 11
--校验码
DECLARE @Parity as varchar(1)
SELECT @Parity =
CASE @Mod
WHEN 0 THEN '1'
WHEN 1 THEN '0'
WHEN 2 THEN 'X'
WHEN 3 THEN '9'
WHEN 4 THEN '8'
WHEN 5 THEN '7'
WHEN 6 THEN '6'
WHEN 7 THEN '5'
WHEN 8 THEN '4'
WHEN 9 THEN '3'
WHEN 10 THEN '2'
END --完整的18位身份证号码
SET @TmpCard = @TmpCard + @Parity IF @IsOld = 1
SET @Valid = 1
ELSE
IF @Parity = RIGHT(@Input, 1) /*校验*/
SET @Valid = 1 --无论对错,都给出有效身份证号码
SET @IDCard = @tmpCard
Finish:
INSERT INTO @TCard VALUES(@Input, @IDCard, @Valid)
RETURN
END
returns char(1)
as
begin
declare @r varchar(2)
declare @i int
if len(@sfzh) <> 18
set @r = 0
else
set @i = cast(substring(@sfzh,1,1) as int) * 7
+cast(substring(@sfzh,2,1) as int) * 9
+cast(substring(@sfzh,3,1) as int) * 10
+cast(substring(@sfzh,4,1) as int) * 5
+cast(substring(@sfzh,5,1) as int) * 8
+cast(substring(@sfzh,6,1) as int) * 4
+cast(substring(@sfzh,7,1) as int) * 2
+cast(substring(@sfzh,8,1) as int) * 1
+cast(substring(@sfzh,9,1) as int) * 6
+cast(substring(@sfzh,10,1) as int) * 3
+cast(substring(@sfzh,11,1) as int) * 7
+cast(substring(@sfzh,12,1) as int) * 9
+cast(substring(@sfzh,13,1) as int) * 10
+cast(substring(@sfzh,14,1) as int) * 5
+cast(substring(@sfzh,15,1) as int) * 8
+cast(substring(@sfzh,16,1) as int) * 4
+cast(substring(@sfzh,17,1) as int) * 2
set @i = @i - @i/11 * 11
set @r = cast((case @i
when 0 then 1
when 1 then 0
when 2 then 11
when 3 then 9
when 4 then 8
when 5 then 7
when 6 then 6
when 7 then 5
when 8 then 4
when 9 then 3
when 10 then 2
else '' end) as char)
if (@r = 11) set @r='X'
else set @r = @r
set @r = '' + @r +''
return @r
end
where dbo.GetCheckIDCardCode(身份证字段名)='X'
主要验证SQL数据库中已输入的15位 及18位 身份证号码的位数、出生年月日是否正确,可以过滤出大部分的输入错误。or (len(身份证号)=主要验证SQL数据库中已输入的15位 及18位 身份证号码的位数、出生年月日是否正确
,可以过滤出大部分的输入错误。or (len(身份证号)=18 and (Substring(身份证号,7,2)<‘19‘ or Substring(身份证号,7,2)>‘20‘or (Substring(身份证号,11,2)>12)or (Substring(身份证号,11,2) in (01,03,05,07,08,10,12) and Substring(身份证号,13,2)>31)or (Substring(身份证号,11,2) in (04,06,09,11) and Substring(身份证号,13,2)>30)or (Substring(身份证号,11,2)=02 and Substring(身份证号,13,2)>29)))下面是针对 15位 及18位 身份证号码性别的验证语句Access 不支持 Substring 查询,可以替换为 mid 查询。select 序号,姓名,身份证号,性别from 身份表where (((len(身份证号)=15) and (Substring(身份证号,15,1) in (1,3,5,7,9)) and 性别<>‘男‘)or ((len(身份证号)=15) and (Substring(身份证号,15,1) in (2,4,6,8,0)) and 性别<>‘女‘))or (((len(身份证号)=18) and (Substring(身份证号,17,1) in (1,3,5,7,9)) and 性别<>‘男‘)or ((len(身份证号)=18) and (Substring(身份证号,17,1) in (2,4,6,8,0)) and 性别<>‘女‘))注:转载请注明出处下面是针对 15位 及18位 身份证号码位数与出生年月日的验证Access 不支持 Substring 查询,可以替换为 mid 查询。select 序号,姓名,身份证号,性别from 身份表where (len(身份证号)<>15 and len(身份证号)<>18)or (len(身份证号)=15 and ((Substring(身份证号,9,2)>12)or (Substring(身份证号,11,2) > 31)or (Substring(身份证号,9,2) in (01,03,05,07,08,10,12) and Substring(身份证号,11,2)>31)or (Substring(身份证号,9,2) in (04,06,09,11) and Substring(身份证号,11,2)>30)or (Substring(身份证号,9,2)=02 and Substring(身份证号,11,2)>29)))