[오라클] 전화번호 포맷 변경 쿼리 (하이픈, 자르기)

오라클(ORACLE)에서 전화번호를 저장 시 하이픈 없이 숫자만을 저장하는 경우가 많다. 전화번호를 조회해서 보기 좋게 하이픈을 다시 넣어서 조회를 해야하는 경우가 많다.

 

오라클 10g 이상의 버전에서는 정규식 함수(REGEXP_REPLACE)를 사용하여 전화번호 포맷을 쉽게 변경할 수 있다.

 

핸드폰 번호만 있을 경우

WITH temp_table AS ( 
    SELECT '01023456789' tel_no FROM dual UNION ALL 
    SELECT '0114567890'  tel_no FROM dual UNION ALL 
    SELECT '0161234567'  tel_no FROM dual 
) 


SELECT tel_no 
     , REGEXP_REPLACE(tel_no, '(.{3})(.+)(.{4})', '\1-\2-\3') tel_no2 
  FROM temp_table  
  

=>REGEXP_REPLACE(tel_no, '(문자열앞3자리)(나머지문자열)(문자열뒤4자리)', '\1-\2-\3')

핸드폰 번호는 [3자리 + (3자리 또는 4자리) + 4자리] 의 형식 이다.

 

핸드폰 번호만 있을 경우

 

일반 전화번호 + 핸드폰 번호

WITH temp_table AS ( 
    SELECT '027891234'   tel_no FROM dual UNION ALL 
    SELECT '0314567890'  tel_no FROM dual UNION ALL 
    SELECT '01023456789' tel_no FROM dual 
) 


SELECT tel_no 
     , REGEXP_REPLACE(tel_no, '(02|.{3})(.+)(.{4})', '\1-\2-\3') tel_no2 
  FROM temp_table
  

=>REGEXP_REPLACE(tel_no, '(예외지역번호(02) 또는 문자열앞3자리)(나머지문자열)(문자열뒤4자리)', '\1-\2-\3')

서울 지역번호(02)를 제외하고는 모두 전화번호의 앞자리는 모두 3자리 이다. 예외의 전화번호 앞자리가 있으면 02|0505|~~~를 추가하면 된다.

 

일반 전화번호 + 핸드폰 번호