综合

SQL 2005身份证函数包含验证和15位转18位

2019-12-02 19:03:34来源:励志吧0次阅读

本文主要介绍了SQL Server 2005的身份证函数,其中包含验证和15位转18位。

具体示例代码如下:

USE [LzmTWWorks]GO/****** 对象: UserDefinedFunction [Helper].[IDCard] 脚本日期: 12/13/2007 19:16:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [Helper].[IDCard] ( @Card varchar(18))RETURNS @TCard TABLE ( Input varchar(18) ,IDCard varchar(18) ,Valid bit)ASBEGIN 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 @ModWHEN 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 + @ParityIF @IsOld = 1SET @Valid = 1ELSE IF @Parity = RIGHT(@Input, 1) /*校验*/SET @Valid = 1--无论对错,都给出有效身份证号码SET @IDCard = @tmpCardFinish: INSERT INTO @TCard VALUES(@Input, @IDCard, @Valid) RETURN END

南京治疗癫痫病医院
昆明看妇科医院哪个好
信阳男科医院
克东县人民医院
长春银屑病初期怎样治疗
分享到: