基于 PB 和 Sybase 环境的公民身份号码升位方法（深圳：独孤求败 2003-05-27）- 计算机世界日报发表的四川省绵阳市胥永康的“公民身份号码升位在 SYBASE 数据库上的实现”文章描述了升位算法，本文根据算法提出可以直接使用的 Sybase 和 PB 的两个过程和函数，用来解决应用系统数据库中身份号升位和客户端程序身份号的自适应问题，在升位的同时充分考虑了合法性的检验。 - 根据中华人民共和国国家标准 GB 11643-1999中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。校验码根据组合码的前十七位数字，采用 ISO7064:1983,MOD 11-2 校验码系统，算法利用 TSQL 描述如下： update tablename set sfzhcolumn=substring(sfzh,1,6)+19+substring(sfzh,7,9)+case convert(int,substring(sfzh,1,1)*7+convert(int,substring(sfzh,2,1)*9+convert(int,substring(sfzh,3,1)*10+convert(int,substring(sfzh,4,1)*5+convert(int,substring(sfzh,5,1)*8+convert(int,substring(sfzh,6,1)*4+1*2+9*1+convert(int,substring(sfzh,7,1)*6+convert(int,substring(sfzh,8,1)*3+convert(int,substring(sfzh,9,1)*7+convert(int,substring(sfzh,10,1)*9+convert(int,substring(sfzh,11,1)*10+convert(int,substring(sfzh,12,1)*5+convert(int,substring(sfzh,13,1)*8+convert(int,substring(sfzh,14,1)*4+convert(int,substring(sfzh,15,1)*2 % 11 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- 注：该算法修改引自计算机世界报四川省绵阳市胥永康的“公民身份号码升位在 SYBASE 数据库上的实现”文章，特别致谢。语句中 CASE 仅适用于 Sybase11.5 以上，%为 Sybase 取模运算符 - 应用系统和生产系统中身份证由于各种原因，升位可能必须考虑以下几个问题，特别是百万级数据库应用系统：(1)错号（长度不足；日期部分非法；含有非数字字符等）(2) 对于大量数据，用单一的 UPDATE 语句可能造成日志和临时空间的溢出(3)升位必须兼顾 Server（Sybase）和 Client（PB）端，Client 端同时必须兼容 15、17、18 位身份号，同时具有自校验(4)在升位原数据时，根据实际情况应该先删除有关索引或触发器，保证升位的快速准确，升位结束后再创建 - Sybase 升位存储过程： Sybase Procedure/输入参数：sfz_input 需要升位的身份证/输入参数：sfz_output 升位后的身份证/根据实际应用，该过程可以直接调用或内嵌create procedure p_sfzto18 sfz_input varchar(18),sfz_output varchar(18) outputasdeclare sfz_doing varchar(18),count integer,valid integerselect sfz_doing = rtrim(ltrim(sfz_input)/将输入身份证赋给中间处理变量if (char_length(sfz_doing) 15) and (char_length(sfz_doing) 17) and (char_length(sfz_doing) 18)/身份证长度合法性校验beginselect sfz_output = sfz_inputreturnendselect sfz_doing = case char_length(sfz_doing)/转化为十七位身份证（没有校验位）when 15 then substring(sfz_doing,1,6)+19+substring(sfz_doing,7,9)when 18 then substring ( sfz_doing ,1,17) else sfz_doing endselect count = 17,valid = 0while count 0begin/非数字字符的合法性校验if ascii(substring(sfz_doing,count,1)57beginselect sfz_output = sfz_inputreturnendselect valid = valid + convert(integer,substring(sfz_doing,count,1) * (case (19 - count) when 1 then 1 when 2then 2 when 3 then 4when 4 then 8 when 5 then 5 when 6 then 10when 7 then 9 when 8 then 7 when 9 then 3 when 10 then6 when 11 then 1 when 12 then 2 when 13then 4 when 14 then 8 when 15 then 5 when 16 then 10 when17 then 9 when 18 then 7 end)select count = count - 1endselect sfz_output = sfz_doing + case valid%11when 0 then 1 when 1 then 0 when 2 then X when 3 then 9when 4 then 8 when 5 then 7when 6 then 6 when 7 then 5 when 8 then 4 when 9 then 3when 10 then 2 end/对出生日期的合法性校验if convert(integer, substring(sfz_doing, 13, 2) 31 or convert(integer, substring(sfz_doing, 11, 2) 12beginselect sfz_output = sfz_inputreturnendelsebeginif substring(sfz_doing,11,2) = 02beginif convert(integer,substring(sfz_doing,13,2) 29beginselect sfz_output = sfz_inputreturnendif convert(integer,substring(sfz_doing, 13, 2) = 29 and not ( (convert(integer, substring(sfz_doing, 7, 4) % 4 = 0 Andconvert(integer,substring(sfz_doing,7,4)%100 0)Or (convert(integer,substring(sfz_doing,7,4) % 400 = 0) ) beginselect sfz_output = sfz_inputreturnendendif convert(integer,substring(sfz_doing,13,2)= 31 and substring(sfz_doing,11,2) not in (01,03,05,07,08,10,12)beginselect sfz_output = sfz_inputreturnend end;PB 身份号自适应函数：PowerBuilder script输入参数：as_sfz_i输出：空串非法，否则为十八位身份证f_convert18(as_sfz_i)string ls_sfz,ls_sign=,ls_calculatesignint li_valid,li_count,li_charp,li_resultls_sfz = trim(as_sfz_i)if len(ls_sfz)15 and len(ls_sfz)17 and len(ls_sfz)18 thenmessagebox(“提示信息“,“录入的身份证长度不正确!“)return “end ifif len(ls_sfz) = 15 thenls_sfz=left(ls_sfz,6)+19+right(ls_sfz,9)end ifif len(ls_sfz) = 18 thenls_sign = right(ls_sfz,1)ls_sfz = left(ls_sfz,17)end ifif not isdate(mid(ls_sfz,7,4)+-+mid(ls_sfz,11,2)+-+mid(ls_sfz,13,2) thenmessagebox(“提示信息“,录入的身份证出生年月信息不合法!)end ifli_valid = 0for li_count = 17 to 1 step -1if not isnumber(mid(ls_sfz,li_count,1) thenmessagebox(“提示信息“,“录入有非数字字符!“)return “end ifli_valid=li_valid+mod(2(li_count),11)*integer(mid(ls_sfz,18 - li_count,1)nextli_charp = mod(li_valid,11)choose case li_charp case 0 ls_calculatesign = 1 case 1 ls_calculatesign = 0 case 2 ls_calculatesign = X case 3 ls_calculatesign = 9 case 4 ls_calculatesign = 8 case 5 ls_calculatesign = 7 case 6 ls_calculatesign = 6 case 7 ls_calculatesign = 5 case 8 ls_calculatesign = 4 case 9 ls_calculatesign = 3 case 10 ls_calculatesign = 2 end chooseif len(ls_sign) = 1 and ls_sign ls_calculatesign thenmessagebox(“提示信息“,录入的身份证不符合校验规则!)end