SQLServer2005 身份证函数,含验证和15位转18位
发布时间：2020-12-12 16:02:04 所属栏目：MsSql教程 来源：网络整理
导读：Author: 水如烟 SQLServer2005 身份证函数,含验证和15位转18位 USE ? [ LzmTWWorks ] GO /* *****?对象:??UserDefinedFunction?[Helper].[IDCard]????脚本日期:?12/07/2007?23:21:37?***** */ SET ?ANSI_NULLS? ON GO SET ?QUOTED_IDENTIFIER? ON GO CREATE
|
Author:水如烟 SQLServer2005 身份证函数,含验证和15位转18位 USE ? [ LzmTWWorks ]GO /* *****?对象:??UserDefinedFunction?[Helper].[IDCard]????脚本日期:?12/07/2007?23:21:37?***** */ 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 使用用下列语句,可以列出所有不符的身份证号码. SELECT????? [ 姓名 ] ????, [ 身份证号 ] ????,b. * FROM ? [ EmployeeWorks ] . [ Base ] . [ 职员 ] ? CROSS ?APPLY? [ LzmTWWorks ] . [ Helper ] . [ IDCard ] (身份证号)?b WHERE ? NOT ? [ 身份证号 ] ? IS ? NULL ? AND ?Valid? = ? 0 20071217 补充一些信息 USE ? [ LzmTWWorks ]GO set ?ANSI_NULLS? ON set ?QUOTED_IDENTIFIER? ON GO ALTER ? FUNCTION ? [ Helper ] . [ IDCard ] ? ( ???? @Card ???? varchar ( 18 ) ) RETURNS ? @TCard ? TABLE ? ( ?????Input???? varchar ( 18 ) ????,Sex???? bit ????,Birthday? varchar ( 8 ) ????,Region???? varchar ( 6 ) ????,RegionName? nvarchar ( 50 ) ????,RegionFullName? nvarchar ( 100 ) ????,Valid???? bit ) AS BEGIN ???? DECLARE ???? ????????????? @Input ???????? varchar ( 18 ) ????????????, @IDCard ???? varchar ( 18 ) ????????????, @Sex ???????? bit ????????????, @Birthday ???? varchar ( 8 ) ????????????, @Region ???? varchar ( 6 ) ????????????, @RegionName ? varchar ( 50 ) ????????????, @RegionFullName ? varchar ( 100 ) ????????????, @Valid ???????? bit ???? DECLARE ????? ????????????? @Length ???? as ? smallint ????????????,? 17 )? /* 取前17位 */ ???????????? SET ? @IsOld ? = ? 0 ???????? END ???? SET ? @Birthday ? = ? SUBSTRING ( @TmpCard ,? 1 )? /* 校验 */ ???????????? SET ? @Valid ? = ? 1 ???? -- 无论正确与否，都给出有效身份证号码 ???? SET ? @IDCard ? = ? @tmpCard ???? -- 取其它信息 ???? SET ? @Sex ? = ? SUBSTRING ( @tmpCard ,? 17 ,? 1 )? % ? 2 ???? SET ? @Region ? = ? SUBSTRING ( @tmpCard ,? 1 ,? 6 ) ???? SELECT ? ????????? @RegionName ? = ? [ Name ] ????????, @RegionFullName ? = ? [ Full ] ???? FROM ? [ Helper ] . [ RegionCodeFullName ] ( Default ,? @Region )? /* 从最新版本数据中取区域信息 */ ???? IF ? @RegionName ? IS ? NULL ???? BEGIN ???????? DECLARE ? @FirstDate ? varchar ( 8 )? /* 第一代身份证的区划码，有许多现在已经不用了。为此，要从最旧版本数据中取区域信息 */ ???????? SELECT ? @FirstDate ? = ? MIN (FirstDate)? ???????? FROM ? [ Private ] . [ RegionCode ] ???? ???? ???????? SELECT ? ????????????? @RegionName ? = ? [ Name ] ????????????, @RegionFullName ? = ? [ Full ] ???????? FROM ? [ Helper ] . [ RegionCodeFullName ] ( ????????????? @FirstDate ????????????, @Region )???? ???? END Finish: ???? INSERT ? INTO ? @TCard ? ???? VALUES (? ????????? @Input ????????, @IDCard ????????, @Sex ????????, @Birthday ????????, @Region ????????, @RegionName ????????, @RegionFullName ????????, @Valid )???? ???? RETURN ? END 示例: SELECT ? * ? FROM ? [ LzmTWWorks ] . [ Helper ] . [ IDCard ] ?( ' 110116200808080010 ' )/* Input??????????????IDCard?????????????Sex???Birthday?Region?RegionName???RegionFullName????Valid ------------------?------------------?-----?--------?------?------------?--------------?----- 110116200808080010?110116200808080014?1?????20080808?110116?怀柔区???????北京市怀柔区????0 */ （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |