[SQL] 주민번호 마스킹 처리

요즘은 주민번호와 같은 개인정보를 화면에 표시할 때 마스킹 처리하여 보여줘야 합니다.

오라클 REGEXP_REPLACE() 함수를 이용해서 처리하여 보았습니다.

 

• OS :  Windows 10 64bit

• DataBase : Oracle 11g 64bit

SELECT REGEXP_REPLACE('1111112999999', '(\d{6})(\d{7})', '\1-\2') AS 주민번호
    , REGEXP_REPLACE('1111112999999', '\d', '*','7') AS MASKING1
    , REGEXP_REPLACE('1111112999999', '(\d{6})(\d{7})', '\1-*******') AS MASKING2
    , REGEXP_REPLACE('1111112999999', '(\d{6})(\d{1})(\d{6})', '\1-\2' || '******') AS MASKING3
    , REGEXP_REPLACE('1111112999999', '(\d{6})(\d{1})(\d{6})', '\1-\2******') AS MASKING4
FROM DUAL
;

 

모든 데이터가 자릿수에 맞게 있지 않을 수도 있기 때문에 이렇게 변경해보았습니다.

-- 마스킹처리
SELECT REGEXP_REPLACE('1', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN1 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('11', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN2 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('111', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN3 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('1111', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN4 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('11111', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN5 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('111111', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN6 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('1111112', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN7 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('11111129', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN8 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('111111299', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN9 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('1111112999', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN10 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('11111129999', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN11 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('111111299999', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN12 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('1111112999999', '(\d{6})(\d{1})(\d{0,7})', '\1-\2' || '******') AS LEN13 FROM DUAL
;

-- 일반
SELECT REGEXP_REPLACE('1', '(\d{6})(\d{1,7})', '\1-\2') AS LEN1 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('11', '(\d{6})(\d{1,7})', '\1-\2') AS LEN2 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('111', '(\d{6})(\d{1,7})', '\1-\2') AS LEN3 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('1111', '(\d{6})(\d{1,7})', '\1-\2') AS LEN4 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('11111', '(\d{6})(\d{1,7})', '\1-\2') AS LEN5 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('111111', '(\d{6})(\d{1,7})', '\1-\2') AS LEN6 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('1111112', '(\d{6})(\d{1,7})', '\1-\2') AS LEN7 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('11111129', '(\d{6})(\d{1,7})', '\1-\2') AS LEN8 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('111111299', '(\d{6})(\d{1,7})', '\1-\2') AS LEN9 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('1111112999', '(\d{6})(\d{1,7})', '\1-\2') AS LEN10 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('11111129999', '(\d{6})(\d{1,7})', '\1-\2') AS LEN11 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('111111299999', '(\d{6})(\d{1,7})', '\1-\2') AS LEN12 FROM DUAL
UNION ALL
SELECT REGEXP_REPLACE('1111112999999', '(\d{6})(\d{1,7})', '\1-\2') AS LEN13 FROM DUAL
;