基于pb和sybase環(huán)境公民身份號碼升位方法

基于PB和Sybase環(huán)境的公民號碼升位方（：獨孤求敗2003-05-----《計算機世界日報》的省綿陽市胥永康的“公民號碼升位在SYBASE數(shù)據(jù)庫上的實現(xiàn)”文章描述了升位算法，本文根據(jù)算法提出可以直接使用的SybasePB用系統(tǒng)數(shù)據(jù)庫中號升位和客戶端程序號的自適應(yīng)問題，在升位的同時充分考慮了的檢驗。----根據(jù)〖中民GB11643-1999〗中有關(guān)公民號碼的規(guī)定，公民號碼是064:1983,MOD11-2系統(tǒng)，算法利用TSQL描述如下：updatetablenamesetsfzhcolumn=caseconvert(int,substring(sfzh,1,1))*2%012thenwhenthen456thenwhenthen8910then----注：該算法修改引自《計算機世界報》省綿陽市胥永康的“公民號碼升位在SYBASE數(shù)據(jù)庫上的實現(xiàn)”文章，特別致謝。語句中CASE僅適用于Sybase11.5上，%為Sybase算符----應(yīng)用系統(tǒng)和生產(chǎn)系統(tǒng)中由于，升位可能必須考慮以下幾個問題，特別是百萬級數(shù)據(jù)庫應(yīng)用系統(tǒng)：(1)錯號（長度不足；日期部分；含有非數(shù)字字符等）(2)對于大量數(shù)據(jù)，用單一的UP端同時必須兼容15、17、18位號，同時具有自校驗(4)在升位原數(shù)據(jù)時，根據(jù)實際情況應(yīng)該先刪除有----Sybase升 Sybase//輸入?yún)?shù)：@sfz_input需要升位的//輸入?yún)?shù)：@sfz_output升位后的createprocedurep_sfzto18@sfz_inputvarchar(18),@sfz_outputvarchar(18)outputdeclare@sfz_ngvarchar(18),@countinteger,@validintegerselect@sfz_ng=//將輸入賦給中間處理變if(char_length(@sfz_ng)<>15)and(char_length(@sfz_ng)<>17)and(char_length(@sfz_ng)<>18)//長度校select@sfz_output=@sfz_inputselect@sfz_ng=casechar_length(@sfz_//轉(zhuǎn)化為十七位（沒有校驗位）when15thenwhen18thensubstring(@sfz_ng,1,17)else@sfz_ngendselect@count=17,@valid=0while@count>0//非數(shù)字字符的校ifascii(substring(@sfz_ng,@count,1))<48orascii(substring(@sfz_ng,@count,1))>57select@sfz_output=@sfz_inputselect@valid=@valid+convert(integer,substring(@sfz_ng,@count,1))*(case(19whenthenwhen2344855when67987when9then3when106when11then1when12then2whenthen4when14then8whenthen5when16then1017then9when18then7end)select@count=@count-1select@sfz_output=@sfz_ng+case01'0'when2then34567'5'when8then9when10then'2'//對出生日期的校ifconvert(integer,substring(@sfz_ng,13,2))31orconvert(integer,substring(@sfz_ng,11,2))>12select@sfz_output=@sfz_inputifsubstring(@sfz_ng,11,2)='02'ifconvert(integer,substring(@sfz_ng,13,2))>29select@sfz_output=@sfz_inputifconvert(integer,substring(@sfz_13,2))=29andnot(substring(@sfz_ng,7,4))%4=0Andconvert(integer,substring(@sfz_ng,7,4))%100<>0)Or(convert(integer,substring(@sfz_ng,7,4))%400=0))select@sfz_output=@sfz_inputifconvert(integer,substring(@sfz_=31andsubstring(@sfz_ng,11,2)notinselect@sfz_output=@sfz_input;PowerBuilderscript輸出：空串，否則為十八位stringls_sfz,ls_sign='',ls_calculatesignintli_valid,li_count,li_charp,li_resultls_sfz=iflen(ls_sfz)<>15andlen(ls_sfz)<>17andlen(ls_sfz)<>18then"錄入的長度不正確!!!")return""endiflen(ls_sfz)=15thenendiflen(ls_sfz)=18thenls_sign=right(ls_sfz,1)ls_sfz=left(ls_sfz,17)endififnotisdate(mid(ls_sfz,7,4)+'-+mid(ls_sfz,11,2)+'-'+mid(ls_sfz,13,2))then'錄入的出生年月信息不合法!!!')endifli_valid=forli_count=17to1step-1ifnotisnumber(mid(ls_sfz,li_count,1))thenreturn"" 11)*integer(mid(ls_sfz,18-li_count,1))li_charp=mod(li_valid,11)choosecaseli_charpcase=case=case