Postgresql 身份证 15位转18位 校验 函数

分类

SQL

CREATE OR REPLACE FUNCTION sfz15to18check(VARCHAR)
  RETURNS TABLE(idcard VARCHAR, judge INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
  idcard        VARCHAR;
  is_idcard     INTEGER;
  v_i           INTEGER;
  idcard_length INTEGER;
  v_s           VARCHAR;
  v_sum         INTEGER;
  v_array1      INTEGER [];
  v_array2      VARCHAR [];
BEGIN
  idcard := trim($1);
  idcard_length := char_length(idcard);
  v_array1 := ARRAY [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2];
  v_array2 := ARRAY ['1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'];

  IF idcard_length = 15
  THEN
    IF idcard ~ '^[123456789]\d{5}\d{2}(0\d|10|11|12)(0\d|1\d|2\d|30|31)\d{3}'
    THEN
      /*15位转18位 加 19 前缀*/
      idcard := overlay(idcard PLACING '19' FROM 7 FOR 0);
      /*15位转18位 加校验码*/
      v_i := 1;
      v_sum := 0;
      LOOP
        v_s := substr(idcard, v_i, 1);
        v_sum := v_sum + cast(v_s AS INTEGER) * v_array1 [v_i];
        v_i := v_i + 1;
        IF v_i > 17
        THEN
          EXIT;
        END IF;
      END LOOP;
      v_sum := mod(v_sum, 11) + 1;
      v_s := v_array2 [v_sum];
      idcard := concat(idcard, v_s);
      is_idcard := 1;
    ELSE
      is_idcard := -1;
    END IF;

  ELSEIF idcard_length = 18
    THEN
      idcard := upper(idcard);
      IF idcard ~ '^[123456789]\d{5}(19|20)\d{2}(0\d|10|11|12)(0\d|1\d|2\d|30|31)\d{3}[\dX]'
      THEN
        /*检测 最后一位加校验码是否正确*/
        v_i := 1;
        v_sum := 0;
        LOOP
          v_s := substr(idcard, v_i, 1);
          v_sum := v_sum + cast(v_s AS INTEGER) * v_array1 [v_i];
          v_i := v_i + 1;
          IF v_i > 17
          THEN
            EXIT;
          END IF;
        END LOOP;
        v_sum := mod(v_sum, 11) + 1;
        v_s := v_array2 [v_sum];
        IF v_s = substr(idcard, 18, 1)
        THEN
          is_idcard := 1;
        ELSE
          is_idcard := -1;
        END IF;
      ELSE
        is_idcard := -1;
      END IF;
  END IF;
  RETURN QUERY SELECT
                 idcard,
                 is_idcard;
END;
$$

只用于18位身份证检测


CREATE FUNCTION sfz_check(CHARACTER VARYING)
  RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
  idcard        VARCHAR;
  is_idcard     INTEGER;
  v_i           INTEGER;
  idcard_length INTEGER;
  v_s           VARCHAR;
  v_sum         INTEGER;
  v_array1      INTEGER [];
  v_array2      VARCHAR [];
BEGIN
  idcard := $1;
  idcard_length := char_length(idcard);
  v_array1 := ARRAY [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2];
  v_array2 := ARRAY ['1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'];

  IF idcard_length = 18
  THEN
    IF idcard ~ '^[123456789]\d{5}(19|20)\d{2}(0\d|10|11|12)(0\d|1\d|2\d|30|31)\d{3}[\dX]'
    THEN
      v_i := 1;
      v_sum := 0;
      LOOP
        v_s := substr(idcard, v_i, 1);
        v_sum := v_sum + cast(v_s AS INTEGER) * v_array1 [v_i];
        v_i := v_i + 1;
        IF v_i > 17
        THEN
          EXIT;
        END IF;
      END LOOP;
      v_sum := mod(v_sum, 11) + 1;
      v_s := v_array2 [v_sum];
      IF v_s = substr(idcard, 18, 1)
      THEN
        is_idcard := 1;
      ELSE
        is_idcard := -1;
      END IF;
    ELSE
      is_idcard := -1;
    END IF;
  END IF;
  RETURN is_idcard;
END;
$$;