주민등록번호 검증 SQL 쿼리

고객분석업무를 하는 경우 드물게 고객의 주민등록번호 유효성 검증을 할 필요가 생깁니다. 정제된 정보만 저장된다면 좋겠지만 실명확인이 여의치 않은 채널을 거친 경우 이상치들이 들어 있을 수 밖에 없습니다. 이 때 주민등록번호의 생성원리를 이용해서 역으로 그 유효성을 검증할 수 있을텐데요, 아래와 같은 Query를 생각해 볼 수 있겠습니다. 주민등록번호가 SSNUM, 문자형 칼럼이라고 생각합시다. 검증원리는 '주민등록번호 검증원리'로 검색해 보시면 무수히 많은 페이지에서 잘 설명하고 있기 때문에 생략하겠습니다. Where문에서 translate함수를 사용한 이유는 0~9 이외의 값이 들어있는 관측치를 제외하기 위해섭니다.

PROCSQL;

CONNECTTO ODBC(DSN="DSNAME" USER=USER_ID PASS=USER_PASS);

 

CREATETABLE CHK_SSNUM ASSELECT * FROM CONNECTION TO ODBC(

SELECT cust_id

            ,case WHEN translate(ssnum,' ','0') = '0'   THEN0

                    WHEN (11 - mod(to_number(substr(ssnum,1,1))*2 +

                                               to_number(substr(ssnum,2,1))*3 +

                                               to_number(substr(ssnum,3,1))*4 +

                                               to_number(substr(ssnum,4,1))*5 +

                                               to_number(substr(ssnum,5,1))*6 +

                                               to_number(substr(ssnum,6,1))*7 +

                                               to_number(substr(ssnum,7,1))*8 +

               to_number(substr(ssnum,8,1))*9 +

                                               to_number(substr(ssnum,9,1))*2 +

                                               to_number(substr(ssnum,10,1))*3 +

                                               to_number(substr(ssnum,11,1))*4 +

                                               to_number(substr(ssnum,12,1))*5,11) ) = to_number(substr(ssnum,13,1)) THEN1

                  WHEN MOD((11 - mod(to_number(substr(ssnum,1,1))*2 +

                                               to_number(substr(ssnum,2,1))*3 +

                                               to_number(substr(ssnum,3,1))*4 +

                                               to_number(substr(ssnum,4,1))*5 +

                                               to_number(substr(ssnum,5,1))*6 +

                                               to_number(substr(ssnum,6,1))*7 +

                                               to_number(substr(ssnum,7,1))*8 +

                                               to_number(substr(ssnum,8,1))*9 +

                                               to_number(substr(ssnum,9,1))*2 +

                                               to_number(substr(ssnum,10,1))*3 +

                                               to_number(substr(ssnum,11,1))*4 +

                                               to_number(substr(ssnum,12,1))*5,11)),10) = to_number(substr(ssnum,13,1)) THEN1

                  ELSE0 end as parity_code_yn

FROM   CUSTOMER_INFORMAION

WHERE trim(translate(ssnum, '1234567890', '          ')) IS NULL

);

DISCONNECTFROM ODBC;

QUIT;