내국인/외국인 주민등록번호 유효성 검사 함수 (주민번호, 검출, SQL)

출처 https://gent.tistory.com/136

아래의 함수는 내국인과 외국인 주민등록번호의 유효성을 판단하여 0 (주민번호 아님), 1 (주민번호) 을 리턴한다.

<사용자 함수 생성>

CREATE OR REPLACE FUNCTION FT_IS_JUMIN_VALID (IN_JUMIN_NO IN VARCHAR2) RETURN INTEGER
  IS       
    WK_JUMIN_NO VARCHAR2(13);  
    WK_CHECK_BIT VARCHAR2(12) := '234567892345'; --검증값   
    WK_TOTAL NUMBER := 0;
    WK_RESULT NUMBER := 0;
    WK_RETURN INTEGER := 0;  
  BEGIN
    WK_JUMIN_NO := REPLACE(TRIM(IN_JUMIN_NO), '-', '');
    
    FOR i IN 1..12
    LOOP
       WK_TOTAL := WK_TOTAL  
                 + TO_NUMBER(SUBSTR(WK_JUMIN_NO, i, 1)) 
                 * TO_NUMBER(SUBSTR(WK_CHECK_BIT, i, 1));
    END LOOP; 
    
    IF TO_NUMBER(SUBSTR(WK_JUMIN_NO, 7, 1)) IN (5, 6, 7, 8) THEN 
        --외국인
        WK_RESULT := MOD(13 - (MOD(WK_TOTAL, 11)), 10);
    ELSE 
        --내국인
        WK_RESULT := MOD(11 - (MOD(WK_TOTAL, 11)), 10);
    END IF; 
    
    IF TO_NUMBER(SUBSTR(WK_JUMIN_NO,13,1)) = WK_RESULT THEN        
        WK_RETURN := 1;
    ELSE
        WK_RETURN := 0;
    END IF;    
 
    RETURN WK_RETURN;
    
  EXCEPTION
    WHEN OTHERS THEN RETURN 0;
 
  END;

<사용예시>

  - 컬럼에 주민등록번호 형식의 문자만 있을때 (12345612345678 or 123456-1234567)

SELECT *
  FROM USERS
 WHERE FT_IS_JUMIN_VALID(SSN) = 1 --0:가짜 주민번호, 1:정상 주민번호

  - 일반 텍스터 컬럼 (비고 등)에 주민등록번호 형식의 문자가 포함되어 있을때

    정규식을 이용하여 문자를 추출하여 함수에 적용한다

    ( [0-9]{6}-[0-9]{7} : (-)포함 13자리 숫자, [0-9]{13} : 13자리 숫자 )

SELECT REGEXP_SUBSTR(REMARK,'[0-9]{6}-[0-9]{7}'), A.*
  FROM USERS A
 WHERE FT_IS_JUMIN_VALID(REGEXP_SUBSTR(REMARK,'[0-9]{6}-[0-9]{7}')) = 1