在我的数据库里有身份证信息，但是有很多人的身份证信息是错误的，现在希望通过SQL脚本，找到错误的身份证信息，并且将其改正为符合身份证编码规则的错误身份证号！
注意，只需要符合编码规则就行，不用是真的！生成的符合规则的假身份证的基础就是原来错误身份证的前十七位或者除性别位及校验码外的十六位！
新人，没分，还希望大家多多帮忙！
------解决方案--------------------
给你拷一个吧.
下面这个是我们系统用来查询/核对身份证的,还可以得到农历生日,证件是否正确,如果验证位错误还可以算出一个正确的验证位.当然还包括户籍地那些.我没给出农历的函数和身份证户籍表了,你自己找吧.这些东西百度全都有,希望楼主自己多动手去找一下.
- SQL code
ALTER FUNCTION [dbo].[CheckIDCard] ( @Card varchar(18) ) RETURNS @TCard TABLE ( Input varchar(18) ,IDCard varchar(18) ,Sex VARCHAR(2) ,Birthday datetime ,BirthdayString VARCHAR(20) ,LunarDate DATETIME ,LunarString VARCHAR(20) ,Region varchar(6) ,RegionName nvarchar(50) ,RegionFullName nvarchar(100) ,Valid bit ) AS BEGIN DECLARE @Input varchar(18) ,@IDCard varchar(18) ,@Sex VARCHAR(2) ,@Birthday DATETIME ,@birthdaystring VARCHAR(20) ,@LunarDate DATETIME ,@LunarString VARCHAR(20) ,@Region varchar(6) ,@RegionName varchar(50) ,@RegionFullName varchar(100) ,@Valid 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 SET @Birthdaystring = SUBSTRING(@TmpCard, 7, 8) IF ISDATE(@Birthdaystring) = 0 GOTO Finish /*非日期*/ SET @Birthday=CONVERT(DATETIME,SUBSTRING(@birthdaystring,1,4) +'-' + SUBSTRING(@birthdaystring,5,2)+'-'+SUBSTRING(@birthdaystring,7,2)) SELECT @LunarDate=a.LunarDate,@LunarString=a.LunarDateString FROM fn_GetLunar(@Birthday) a --前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 --取其它信息 SET @Sex = case SUBSTRING(@tmpCard, 17, 1) % 2 WHEN 1 THEN '男' else'女' end SET @Region = SUBSTRING(@tmpCard, 1, 6) SELECT @RegionName = dq ,@RegionFullName = dq FROM idcardinfo WHERE [email protected] -- select * from idcardinfo Finish: INSERT INTO @TCard VALUES(@Input,@IDCard,@Sex,@Birthday,@birthdaystring,@LunarDate,@LunarString, @Region,@RegionName,@RegionFullName,@Valid) RETURN END