• 1785阅读
  • 0回复

正则表达式函数验证身份证号码合法性 [复制链接]

上一主题 下一主题
离线韭菜
 

只看楼主 倒序阅读 0楼 发表于: 2010-07-07
/*

18位身份证号校验位算法:
            1:把前17位号码从第高位到低位与下列17个数字分别相乘求和(N):
                『2,4,8,5,10,9,7,3,6,1,2,4,8,5,10,9,7』
                 比如身份证号码为:C1C2C3……C16C17
                 则N=C17×2+C16×4+……+C1×7;
            2:将N除以11取余数R,根据余数计算校验位T:
                 1)如果R=0,则T=1;如果R=1,则T=0;如果R=2,则T=X;
                 2)如果R=3,则T=9;如果R=4,则T=8;依此类推……;如果R=10,则T=2;


*/


create or replace function fn_check_idcard(p_idcard in varchar2)

  /*
  功能:  校验中国大陆身份证号码
  返回值:校验通过返回正确的号码,不通过返回 null
  */

return varchar2 is
  v_sum       number;
  v_mod       number;
  v_checkcode char(11) := '10X98765432';
  v_checkbit  char(1 char);
  v_idcard    varchar(300 char);
  v_last_char char(1 char);
begin

  v_idcard := upper(p_idcard);

  v_idcard := replace(v_idcard, '1', '1');
  v_idcard := replace(v_idcard, '2', '2');
  v_idcard := replace(v_idcard, '3', '3');
  v_idcard := replace(v_idcard, '4', '4');
  v_idcard := replace(v_idcard, '5', '5');
  v_idcard := replace(v_idcard, '6', '6');
  v_idcard := replace(v_idcard, '7', '7');
  v_idcard := replace(v_idcard, '8', '8');
  v_idcard := replace(v_idcard, '9', '9');
  v_idcard := replace(v_idcard, '0', '0');

  if length(v_idcard) = 18 then
  
    v_last_char := substr(v_idcard, 18, 1);
  
    v_last_char := case
                     when v_last_char in ('X',
                                          '*',
                                          '×',
                                          'A',
                                          'B',
                                          'C',
                                          'D',
                                          'E',
                                          'F',
                                          'G',
                                          'H',
                                          'I',
                                          'J',
                                          'K',
                                          'L',
                                          'M',
                                          'N',
                                          'O',
                                          'P',
                                          'Q',
                                          'R',
                                          'S',
                                          'T',
                                          'U',
                                          'V',
                                          'W',
                                          'X',
                                          'Y',
                                          'Z') then
                    
                      'X'
                     else
                      v_last_char
                   end;
  
  end if;

  v_idcard := substrb(v_idcard, 1, 17) || v_last_char;

  if length(v_idcard) = 18 and
     regexp_like(v_idcard, '^\d{18}$|^\d{17}[xX]$') then
  
    v_sum      := (to_number(substrb(v_idcard, 1, 1)) +
                  to_number(substrb(v_idcard, 11, 1))) * 7 +
                  (to_number(substrb(v_idcard, 2, 1)) +
                  to_number(substrb(v_idcard, 12, 1))) * 9 +
                  (to_number(substrb(v_idcard, 3, 1)) +
                  to_number(substrb(v_idcard, 13, 1))) * 10 +
                  (to_number(substrb(v_idcard, 4, 1)) +
                  to_number(substrb(v_idcard, 14, 1))) * 5 +
                  (to_number(substrb(v_idcard, 5, 1)) +
                  to_number(substrb(v_idcard, 15, 1))) * 8 +
                  (to_number(substrb(v_idcard, 6, 1)) +
                  to_number(substrb(v_idcard, 16, 1))) * 4 +
                  (to_number(substrb(v_idcard, 7, 1)) +
                  to_number(substrb(v_idcard, 17, 1))) * 2 +
                  to_number(substrb(v_idcard, 8, 1)) * 1 +
                  to_number(substrb(v_idcard, 9, 1)) * 6 +
                  to_number(substrb(v_idcard, 10, 1)) * 3;
    v_mod      := mod(v_sum, 11);
    v_checkbit := substrb(v_checkcode, v_mod + 1, 1);
  
    if v_checkbit = upper(substrb(v_idcard, 18, 1)) then
      return v_idcard;
    else
      return null;
    end if;
  elsif length(v_idcard) = 15 and regexp_like(v_idcard, '^\d{15}$') then
  
    return v_idcard;
  else
    return null;
  end if;
exception
  when others then
    begin
      --dbms_output.put_line(p_idcard);
      return null;
    end;
end;
/
grant all privileges on *.* to 'a'@'localhost' identified by 'a' with grant option;flush privileges;
快速回复
限100 字节
 
上一个 下一个