ID card, organization code, and business license number verification function

Go/***** object: userdefinedfunction [Ro]. [wj_validateidc] script Date: 09/29/2012 11:23:34 ******/set ansi_nulls ongoset quoted_identifier ongo -- verify the ID number -- editor: -- creation date: -- parameter: @ idc id No. Create Function [Ro]. [wj_validateidc] (@ IDC varchar (18) returns bitasbegin declare @ validfactors varchar (17), @ validcodes varchar (11), @ I tinyint, @ itemp int select @ validfactors = '79a584216425a5842 ', @ validcodes = '10x98765432', @ I = 1, @ itemp = 0 -- Verification bit -- add conditions according to the guaranteed database. Otherwise, an error is reported, by WJ if (@ IDC like 'x % 'or @ IDC like' * % 'or @ IDC like' t % ') Return (0) If Len (@ IDC) <> 15 and Len (@ IDC) <> 18 -- ID card number is only 15 or 18 bits return (0) If Len (@ IDC) = 15 -- if it is a 15-digit ID card, only the verification date if (isdate ('19' + substring (@ IDC, 7,6) = 0 and '19' + substring (@ IDC, 7, 6) between '2014-01-01 'and '2014-01-01') Return (0) else return (1) -- 18-bit ID card verification date check bit if (isdate (substring (@ IDC, 7, 8) = 0 and substring (@ IDC, 1900) between '2017-01-01 'and '2017-01-01') -- Verification date return (0) --- the verification bit starts 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 endgo/****** object: userdefinedfunction [Ro]. [wj_validateyyzzno] script Date: 09/29/2012 11:25:49 *******/set ansi_nulls ongoset quoted_identifier ongo -- function: to verify the Business License number -- editor: -- creation date: -- parameter: @ zzno business license No. Create Function [Ro]. [wj_validateyyzzno] (@ zzno varchar (15) returns bitasbegin declare @ M int, @ n int, @ s int, @ P int, @ I int, @ result int select @ M = 10, @ n = 11, @ P = @ M if (LEN (ltrim (rtrim (@ zzno) = 13 and isnumeric (@ zzno) = 1) Begin return 1 end if (isnumeric (ltrim (rtrim (@ zzno) = 0) Begin return 0 end if (LEN (ltrim (rtrim (@ zzno ))) <> 15) Begin return 0 end set @ I = 1 while @ I <= 14 begin set @ s = substring (@ zzno, @ I, 1) set @ P = @ P + @ s set @ P = @ P % @ M if @ p = 0 begin set @ P = @ M end set @ P = @ p * 2 set @ P = @ P % @ n set @ I = @ I + 1 end set @ P = @ P + substring (@ zzno, 15, 1) set @ P = @ P % @ M if @ p = 1 begin set @ result = 1 end else begin set @ result = 0 endreturn @ resultendgo/***** object: userdefinedfunction [Ro]. [wj_validatezzjgno] script Date: 09/29/2012 11:26:32 *******/set ansi_nulls ongoset quoted_identifier ongo -- function: Organization ID verification -- editor: -- creation date: -- parameter: @ zzjgno: Organization No. Alter function [Ro]. [wj_validatezzjgno] (@ zzjgno varchar (20) returns bitasbegindeclare @ C varchar (20), @ ZZ int, @ Z int, @ I int, @ Jaz varchar (30 ), @ C9 varchar (10) set @ zzjgno = upper (@ zzjgno) set @ I = 1 while @ I <= 8 beginset @ C = substring (@ zzjgno, @ I, 1) if @ C> = 'A' and @ C <= 'Z' begin set @ z = (ASCII (@ C)-55) * (Case @ I when 1 then 3 when 2 then 7 when 3 then 9 when 4 then 10 when 5 then 5 when 6 then 8 when 7 then 4 when 8 then 2 end) end else if @ C> = '0' and @ C <= '9' begin set @ z = convert (numeric, @ C) * (Case @ I when 1 then 3 when 2 then 7 when 3 then 9 when 4 then 10 when 5 then 5 when 6 then 8 when 7 then 4 when 8 then 2 end) endelsebeginreturn 0end set @ ZZ = isnull (@ ZZ, 0) + @ Z set @ I = @ I + 1 endset @ Jaz = 11-(@ ZZ % 11) if @ Jaz = 10 beginset @ C9 = 'X' end else if @ Jaz = 11 beginset @ C9 = '0' endelse beginset @ C9 = ltrim (rtrim (@ Jaz )) end if @ zzjgno = substring (@ zzjgno,) + @ c9begin return 1 end if @ zzjgno = substring (@ zzjgno) + '-' + @ c9begin return 1 endif @ zzjgno = substring (@ zzjgno,) + '-' + @ c9begin return 1 endif @ zzjgno = substring (@ zzjgno) + ''+ @ c9begin return 1 end return 0end

Reprinted from: ID card, organization code, and business license number verification function