Shulou(Shulou.com)06/01 Report--

This article mainly explains the implementation of the legal check function of SqlServer2000 ID card, the content is clear and clear, interested friends can learn, I believe it will be helpful after reading.

Let's take a look at the code for sqlserver2000 ID card verification. The specific code is as follows:

/ * number of identity verification lines * / if exists (select * from sysobjects where name='fun_utils_idnumberoprater' and type='FN') drop function fun_utils_idnumberopratergocreate function fun_utils_idnumberoprater (@ idnumber varchar (50) =') returns varchar (500) as/* citizenship number consists of a 17-digit numeric code and a 1-digit parity code. The order from left to right is 6-bit address code, 8-bit date of birth code, 3-bit sequence code and 1-bit check code. The address code (see appendix below for the ID card address code comparison table) and the birth date code are easy to understand. The sequence code refers to the sequence number assigned to people born on the same day in the same year within the area identified by the same address code. the odd number of the sequence code is assigned to men and even numbers to women. The algorithm of the last bit check code of ID card is as follows: 1. Multiply the first 17 digits of the ID card number by different coefficients, and the coefficients from the first digit to the 17th digit are: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 22. Add the 17 products you get. 3. Divide the added sum by 11 and get the remainder. 4. The remainder may be 0 1 2 3 4 5 6 7 8 9 10, and the corresponding ID card last digit check code is 10 X 9 8 7 6 5 4 3 2. Function: 1. Verify the validity of ID card 2.15-bit ID card is transferred to 18 digits * / begin declare @ ReturnText varchar (1000)-return value, @ Separator varchar (1)-delimiter, @ idnumberElement varchar (1)-each element of ID card @ CurrentIndex int-- current index bit of ID card number, @ NextIndex int-- next index bit of ID card number, @ xsnumber varchar (50)-- coefficient, @ xsElement varchar (2)-- coefficient each element @ xsCurrentIndex int-- current index bit of coefficient, @ xsNextIndex int-- next index bit of coefficient, @ jym varchar (11)-- check code, @ sum int-- summation of ID card element * coefficient @ div int-- Total the remainder of 11, @ CurrentJym varchar (1)-- current check code, @ CurrentWs int-- current ID number, @ NewJym varchar (1000)-- New check code @ NewWs int-- New ID card digits, @ oldId varchar (20)-- processes ID card numbers and verifies the validity of digits set @ Separator =',' Set @ CurrentIndex = 1; set @ xsnumber ='7, 9, 10, 10, 5, 7, 4, 4, 1, 1, 5, 9, 5, 9, 5, 9, 5, 5, 4, 5, 4, 5, 4, 5, 5, 4, 5, 5, 4, 5, 5, 4, 5, 5, 5, 4, 5, 5, 5, 5, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 1, 5, 5, 5, 1, 5, 5, 5, 1, 5, 5, 5, 1 Set @ oldId = @ idnumber; if @ CurrentWs=15 begin set @ idnumber=stuff (@ idnumber,7,0,case when substring (@ idnumber,13,3) in ('999,' else, '19' end); SET @ CurrentJym ='' End else if @ CurrentWs > = 18 begin set @ CurrentJym=substring (@ idnumber,18,1); set @ idnumber=substring (@ idnumber,1,17); end else begin set @ ReturnText=' check failed _ 'goto TheEnd End if isdate (substring (@ idnumber,7,8)) = 0 begin set @ ReturnText=' check failed _ 'goto TheEnd End-calculation of ID card check bit while (@ CurrentIndex 0 set @ sum=@sum+cast (@ idnumberElement as int) * cast (@ xsElement as int) else begin set @ ReturnText=' check failed _' Goto TheEnd end set @ CurrentIndex=@CurrentIndex+1; set @ xsCurrentIndex=@xsNextIndex+1; end set @ div=@sum; set @ NewJym=substring If (@ CurrentJym'') and (@ NewJym@CurrentJym) begin set @ ReturnText=' failed to verify whether the ID card check bit is incorrect Goto TheEnd end-output new ID card number if len (@ oldId) > 18 set @ ReturnText=' check passed. The original ID card length is not 15-bit or 18-bit _'+ (@ idnumber+@NewJym) else set @ ReturnText=' check The original ID card length is'+ cast (len (@ oldId) as varchar) + 'bit _' + (@ idnumber+@NewJym) goto TheEnd TheEnd: return @ ReturnTextendgo-- the following stored procedure is to traverse the entire table Add a field and modify the check-/ * * the stored procedure is based on the original table, add a field to save the verification result, and need to replace the indication and field name * /-- table name: a_idcard, column idcard original ID card, card standard (newly added) Result verification result (newly added)-add standard ID card field beginbeginif not exists (select * from syscolumns where id=object_id ('axiidcard`) and name='card') alter table a_idcard add [card] varchar (20) End-- add check result field beginif not exists (select * from syscolumns where id=object_id ('axiidcard`) and name='result') alter table a_idcard add [result] nvarchar (20) Endendgo-- traversal results BEGINdeclare @ id_card varchar, @ res varchar, @ c_index intdeclare cur cursor for select idcard from a_idcard-- here you need to replace open curfetch next from cur into @ id_cardwhile @ @ FETCH_STATUS = 0 begin set @ res = dbo.fun_utils_idnumberoprater (@ id_card) set @ c_index = charindex ('_', @ res) print substring (@ res,0,@c_index) print substring (@ res, @ c_index+1 Len (@ res)-- here you need to replace update a_idcard set card=substring (@ res, @ c_index+1, len (@ res)), result=substring (@ res,0,@c_index) where idcard=@id_cardfetch next from cur into @ id_cardend close curdeallocate curend go

PS: let's take a look at the ID card validity verification template.

/ / this can verify 15-bit and 18-bit ID cards, and includes birthday and check bit verification. / / if you are interested, you can also add the verification of the location of the ID card, that is, some of the first six digits are legal and some are illegal. Function isIdCardNo (num) {if (num.indexOf ("x")! =-1) {return false;} num = num.toUpperCase (); / / the ID number is 15 or 18 digits, all 15 digits, the first 17 digits of the 18 digits, and the last bit is the check bit, which may be a number or the character X. If (! / (^\ d {15} $) | (^\ d {17} ([0-9] | X) $) / .test (num)) {alert ('the ID number entered is incorrect or the number does not conform to the regulations! \ n15-digit numbers should be all digits, and the last digits of 18-digit numbers can be digits or X. ; return false;} / / check bits are generated according to ISO 7064:1983.MOD 11-2, and X can be thought of as the number 10. / / analyze the birth date and check bit var len, re; len = num.length; if (len = = 15) {re = new RegExp (/ ^ (\ d {6})) (\ d {2}) (\ d {3}) $/); var arrSplit = num.match (re) / / check whether the birthday date is correct var dtmBirth = new Date ('19' + arrSplit [2] +' /'+ arrSplit [3] +'/'+ arrSplit [4]); var bGoodDay; bGoodDay = (dtmBirth.getYear () = = Number (arrSplit [2])) & & (dtmBirth.getMonth () + 1) = Number (arrSplit [3]) & (dtmBirth.getDate () = = Number (arrSplit [4]) If (! bGoodDay) {alert ('incorrect date of birth in the ID number entered!') ; return false;} else {/ / convert 15-bit ID cards to 18-bit / check bits are generated in accordance with the provisions of ISO 7064:1983.MOD 11-2, X can be considered as the number 10. Var arrInt = new Array (7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2); var arrCh = new Array ('1, 8, 4, 2); var nTemp = 0, I Num = num.substr (0,6) + '19' + num.substr (6, num.length-6); for (I = 0; I < 17; iTunes +) {nTemp + = num.substr (I, 1) * arrInt [I];} num + = arrCh [nTemp% 11]; return num }} if (len = = 18) {re = new RegExp (/ ^ (\ d {6}) (\ d {4}) (\ d {2}) (\ d {3}) ([0-9] | X) $/); var arrSplit = num.match (re); / / check whether the birthday date is correct var dtmBirth = new Date (arrSplit [2] + "/" + arrSplit [3] + "/" + arrSplit [4]); var bGoodDay BGoodDay = (dtmBirth.getFullYear () = = Number (arrSplit [2])) & & (dtmBirth.getMonth () + 1) = = Number (arrSplit [3])) & & (dtmBirth.getDate () = = Number (arrSplit [4])); if (! bGoodDay) {alert (dtmBirth.getYear ()); alert (arrSplit [2]); alert ('the birth date entered in the ID card number is incorrect!') ; return false;} else {/ / verify that the check code of the 18-bit ID card is correct. / / check bits are generated according to ISO 7064:1983.MOD 11-2, and X can be thought of as the number 10. Var valnum; var arrInt = new Array (7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2); var arrCh = new Array ('1, 8, 4, 2); var arrCh = new Array (I = 0; I < 17); var nTemp = 0, I; for (I = 0; I < 17) NTemp +) {nTemp + = num.substr (I, 1) * arrInt [I];} valnum = arrCh [nTemp% 11]; if (valnum! = num.substr (17,1)) {alert ('18-bit ID card check code is incorrect! Should be:'+ valnum); return false;} return num;}} return false;} after reading the above content, do you have a further understanding of the implementation of the legal check function of SqlServer2000 ID card? if you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.