求助：批量检验身份证的问题

[tmfhv2008]

一张表有身份证code字段，如何批量检验这些身份证正确与否？

[水族杰纶]

你判断的标准是什么
仅仅是18位还是15位这样？
如果这样
用len好了

[tmfhv2008]

针对18位的

[--小F--]

--通过身份证获得户籍
create function f_getcityfromcid (@cid varchar(18))   
returns varchar(50)   
as  
begin    
  
    declare @acity varchar(1000)   
    set @acity = '____,____,____,____,____,____,____,____,____,____,____,北京__,天津__,河北__,山西__,内蒙古_,____,____,____,____,____,辽宁__,吉林__,黑龙江_,____,____,____,____,____,____,____,上海__,江苏__,浙江__,安微__,福建__,江西__,山东__,____,____,____,河南__,湖北__,湖南__,广东__,广西__,海南__,____,____,____,重庆__,四川__,贵州__,云南__,西藏__,____,____,____,____,____,____,陕西__,甘肃__,青海__,宁夏__,新疆__,____,____,____,____,____,台湾__,____,____,____,____,____,____,____,____,____,香港__,澳门__,____,____,____,____,____,____,____,____,国外__,'  
  
  
    set @cid = upper(@cid)   
  
    IF (len(@cid) <> 18 OR patindex('%[^0-9X]%',@cid) > 0)   
        RETURN '你小子骗我，这不是合法的身份证'  
  
    IF substring(@acity,cast(left(@cid,2) as int)* 5+1,4) = ''    
        RETURN '你小子骗我，这身份证的地区码不存在'  
  
  
    RETURN '这小子是：'+replace(substring(@acity,cast(left(@cid,2) as int)* 5+1,4),'_','')   
  
end   
go   
  
select dbo.f_getcityfromcid('32108519760502ttt9')   
/*  
--------------------------------------------------   
你小子骗我，这不是合法的身份证  
 
（所影响的行数为 1 行）  
 
*/  
select dbo.f_getcityfromcid('32108519****026**9')   
/*  
--------------------------------------------------   
这小子是：江苏  
 
（所影响的行数为 1 行）  
 
 
*/  
drop function f_getcityfromcid

[zhangle861010]

[Quote=引用 3 楼 fredrickhu 的回复:]
SQL code--通过身份证获得户籍
create function f_getcityfromcid (@cid varchar(18))
returns varchar(50)
as
begin

declare @acity varchar(1000)
set @acity = '____,____,____,____,____,____,____,____,____,____,____,北京__,天津__,河北__,山西__,内蒙古_,____,____,____,____,____,辽宁__,吉林__,黑龙江_,____,____,____,____,____,____,____,上海__,江苏__,浙江__,安微__,福建__,江西…
[/Quote]


顶！！

[tmfhv2008]

三楼请加我QQ：921694651，在线请教一下

[tmfhv2008]

有谁知道的？请加我QQ：921694651，在线请教一下

[-无-为-]

xuexi l

[yesyesyes]

身份证有效与否的判断标准在公安局

[claro]

先理解身份证的编号规则。
3楼的可以借鉴。

[ChinaJiaBing]

一张表有身份证code字段，如何批量检验这些身份证正确与否？

你要先知道它的断判标准，然后就好办了..

[ai_li7758521]

---------------如果要严格来校验，

CREATE FUNCTION [fn_ValidateIDC]
(
 @idc varchar(18)
)RETURNS BIT
AS
BEGIN

 DECLARE @validFactors VARCHAR(17),@validCodes VARCHAR(11),@i TINYINT,@iTemp INT
 SELECT @validFactors='79A584216379A5842',@validCodes='10X98765432',@i=1,@iTemp=0
 --验证校验位
 
 IF LEN(@idc)<>15 AND LEN(@idc)<>18--身份证号只有15或18位
  RETURN(0)
 IF LEN(@idc)=15  --如果是15位身份证 则只验证日期
     IF (ISDATE('19'+SUBSTRING(@idc,7,6))=0 and '19'+SUBSTRING(@idc,7,6) between '1900-01-01' and '2010-01-01')
        RETURN(0)
     ELSE
        RETURN(1)
 --18位身份证 验证日期 校验位
 IF (ISDATE(SUBSTRING(@idc,7,8))=0 and SUBSTRING(@idc,7,6) between '1900-01-01' and '2010-01-01')--验证日期
    RETURN(0)
 ---验证校验位开始
 
 WHILE @i<18
    BEGIN
       SELECT @iTemp=@iTemp+CAST(SUBSTRING(@idc,@i,1) AS INT)*
             (CASE SUBSTRING(@validFactors,@i,1) WHEN 'A' THEN 10 ELSE SUBSTRING(@validFactors,@i,1) END)
             ,@i=@i+1
    END
 IF SUBSTRING(@validCodes,@iTemp%11+1,1)=RIGHT(@idc,1)
    RETURN 1
 ELSE
    RETURN 0
 RETURN 0
END
GO


select dbo.fn_ValidateIDC('************')
/**//*
------
**  大家用合法身份证号测试 
*/

[ai_li7758521]

----------------------------------------------------------------
--简单的检验 ，不校验校验位：

 

create FUNCTION [fn_ValidateIDC]
(
@idc varchar(18)
)RETURNS BIT
AS
begin
if  (isnumeric(@idc)=1 and len(@idc)=15 and isdate(substring(@idc ,7,6))=1
and (right(@idc ,1)='0' or right(@idc ,1)='1'))

or (len(@idc )=18 and isnumeric(left(@idc ,17))=1 and isdate(substring(@idc ,7,8))=1 
   and patindex('%[X0-9]%',right(@idc ,1))>0)
    return 1
return 0
    
end

go

select dbo.fn_ValidateIDC('12345619780901231X')
drop function fn_ValidateIDC
--------------------------------------------------------

[ai_li7758521]

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

[lsh2216024]

三楼的是不是这个

aCity={11:"北京",12:"天津",13:"河北",14:"山西",15:"内蒙古",21:"辽宁",22:"吉林",23:"黑龙江",31:"上海",32:"江苏",33:"浙江",34:"安徽",35:"福建",36:"江西",37:"山东",41:"河南",42:"湖北",43:"湖南",44:"广东",45:"广西",46:"海南",50:"重庆",51:"四川",52:"贵州",53:"云南",54:"西藏",61:"陕西",62:"甘肃",63:"青海",64:"宁夏",65:"新疆",71:"台湾",81:"香港",82:"澳门",91:"国外"}