〖中华人民共和国国家标准 GB 11643-1999〗 身份证升位

根据〖中华人民共和国国家标准 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) output
as
declare @sfz_doing varchar(18),
@count integer,@valid integer

select @sfz_doing = rtrim(ltrim(@sfz_input))
//将输入身份证赋给中间处理变量
if (char_length(@sfz_doing) < > 15) and
(char_length(@sfz_doing) < > 17) and
(char_length(@sfz_doing) < > 18)
//身份证长度合法性校验
begin
select @sfz_output = @sfz_input
return
end
select @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 end
select @count = 17,@valid = 0
while @count > 0
begin
//非数字字符的合法性校验
if ascii(substring(@sfz_doing,@count,1))< 48
or ascii(substring(@sfz_doing,@count,1)) >57
begin
select @sfz_output = @sfz_input
return
end
select @valid = @valid + convert(integer,
substring(@sfz_doing,@count,1)) * (case
(19 - @count) when 1 then 1 when 2
then 2 when 3 then 4
when 4 then 8 when 5 then 5 when 6 then 10
when 7 then 9 when 8 then 7
when 9 then 3 when 10 then
6 when 11 then 1 when 12 then 2 when 13
then 4 when 14 then 8 when 15
then 5 when 16 then 10 when
17 then 9 when 18 then 7 end)
select @count = @count - 1
end

select @sfz_output = @sfz_doing + case @valid%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' end
//对出生日期的合法性校验
if convert(integer, substring(@sfz_doing, 13, 2)) >
31 or convert(integer, substring(@sfz_doing, 11, 2)) > 12
begin
select @sfz_output = @sfz_input
return
end
else
begin
if substring(@sfz_doing,11,2) = '02'
begin
if convert(integer,
substring(@sfz_doing,13,2)) > 29
begin
select @sfz_output = @sfz_input
return
end
if convert(integer,substring(@sfz_doing,
13, 2)) = 29 and not ( (convert(integer,
substring(@sfz_doing, 7, 4)) % 4 = 0 And
convert(integer,substring(@sfz_doing,7,4))%100 < > 0)
　　Or (convert(integer,substring(@sfz_doing,7,4)) % 400 = 0) )
begin
select @sfz_output = @sfz_input
return
end
end
if convert(integer,substring(@sfz_doing,13,2))
= 31 and substring(@sfz_doing,11,2) not in
('01','03','05','07','08','10','12')
begin
select @sfz_output = @sfz_input
return
end
end
;


PB身份号自适应函数：
PowerBuilder script
输入参数：as_sfz_i
输出：空串非法，否则为十八位身份证

f_convert18(as_sfz_i)

string ls_sfz,ls_sign='',ls_calculatesign
int li_valid,li_count,li_charp,li_result

ls_sfz = trim(as_sfz_i)
if len(ls_sfz)< >15 and len(ls_sfz)< >17
and len(ls_sfz)< >18 then
messagebox("提示信息",
"录入的身份证长度不正确!!!")
return ""
end if
if len(ls_sfz) = 15 then
ls_sfz=left(ls_sfz,6)+
'19'+right(ls_sfz,9)
end if
if len(ls_sfz) = 18 then
ls_sign = right(ls_sfz,1)
ls_sfz = left(ls_sfz,17)
end if
if not isdate(mid(ls_sfz,7,4)+'-'
+mid(ls_sfz,11,2)+'-'+mid(ls_sfz,13,2)) then
messagebox("提示信息",
'录入的身份证出生年月信息不合法!!!')
end if
li_valid = 0
for li_count = 17 to 1 step -1
if not isnumber(mid(ls_sfz,
li_count,1)) then
messagebox("提示信息",
"录入有非数字字符!")
return ""
end if
li_valid=li_valid+mod(2^(li_count),
11)*integer(mid(ls_sfz,18 - li_count,1))
next

li_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 choose
if len(ls_sign) = 1 and ls_sign < > ls_calculatesign then
messagebox("提示信息",'录入的身份证不符合校验规则!!!')
end if
return ls_sfz + ls_calculatesign


　　 以上过程和函数，已经成功的在成都社保局YHCSI2000社保管理系统中成功利用，该系统原有数据110万，运行环境为PB6.5，Sybase11.5。