전화번호처럼 사람이 직접 입력한 문자열에서 공백, 하이픈, 점, 괄호 같은 문자를 걷어 내고 숫자만 조회하려면 REGEXP_REPLACE(컬럼명, '[^0-9]', '')를 사용할 수 있습니다. 다만 이 방법은 잘못된 번호를 올바르게 고치는 기능이 아니라, 문자 전송 전에 형식을 일정하게 만드는 조회 단계의 방어 장치입니다.
핵심 답변
[^0-9]는 0부터 9까지의 숫자가 아닌 문자를 뜻합니다. 이를 빈 문자열로 바꾸면 010-1234-5678, 010 1234 5678, 010.1234.5678을 모두 01012345678 형태로 조회할 수 있습니다.
전화번호 데이터가 정형화되지 않는 현실적인 이유
문자 전송 프로그램을 만들다 보면 차트나 기존 업무 화면에 저장된 전화번호를 가져와야 할 때가 있습니다. 문제는 그 값이 한 가지 형식으로만 들어오지 않는다는 점입니다. 누군가는 하이픈을 넣고, 누군가는 공백을 넣습니다. 괄호나 점을 사용하거나 번호 앞뒤에 설명을 덧붙이는 경우도 있습니다.
입력 화면을 새로 설계할 수 있다면 숫자만 허용하고 자리수를 검사하는 편이 바람직합니다. Delphi라면 MaskEdit 같은 입력 도구를 고려할 수 있고, 웹이나 다른 환경에서도 입력 마스크와 서버 측 검증을 함께 적용하는 것이 기본입니다. 그러나 내가 만들지 않은 화면이거나 여러 부서의 협조가 필요한 기존 시스템이라면 모든 입력 경로를 한 번에 바로잡기 어렵습니다.
그럴 때 조회 쿼리에서 형식을 정리하는 방법이 실용적인 보완책이 됩니다. 완벽한 데이터 품질 대책은 아니지만, 발송 모듈이 다양한 표기 방식을 매번 따로 처리하지 않도록 입력값을 한 형태로 모을 수 있습니다.
REGEXP_REPLACE로 숫자만 남기는 기본 쿼리
예제 테이블에는 원본 값을 그대로 보관합니다. 원본을 수정하지 않고 조회 결과에 정규화된 번호를 추가해 차이를 확인하는 구조입니다.
CREATE TABLE contact_sample (
contact_id NUMBER NOT NULL,
phone_text VARCHAR2(100)
);
INSERT INTO contact_sample (contact_id, phone_text)
VALUES (1, '010-1234-5678');
INSERT INTO contact_sample (contact_id, phone_text)
VALUES (2, '010 2345 6789');
INSERT INTO contact_sample (contact_id, phone_text)
VALUES (3, '010.3456.7890');
INSERT INTO contact_sample (contact_id, phone_text)
VALUES (4, '(010) 4567-8901');
INSERT INTO contact_sample (contact_id, phone_text)
VALUES (5, '연락처 없음');예제 목적
다양한 전화번호 표기와 숫자가 없는 값을 한 번에 재현합니다. 테스트용 객체 생성 권한이 필요하며, 운영 테이블이 아닌 개인 테스트 스키마에서 실행하는 예제입니다.
SELECT contact_id,
phone_text,
REGEXP_REPLACE(phone_text, '[^0-9]', '') AS phone_digits
FROM contact_sample
ORDER BY contact_id;| 원본 값 | 조회 결과 | 판단 |
|---|---|---|
| 010-1234-5678 | 01012345678 | 발송 전 추가 검증 대상 |
| (010) 4567-8901 | 01045678901 | 괄호와 공백도 제거 |
| 연락처 없음 | NULL |
발송 대상에서 제외 |
Oracle은 빈 문자열을 NULL로 취급합니다. 따라서 숫자가 전혀 없는 문자열은 모든 문자가 제거된 뒤 NULL이 됩니다. 원본이 NULL인 경우도 결과는 NULL입니다.
문자 발송 대상은 숫자 추출 뒤 한 번 더 검증해야 한다
숫자가 남았다는 사실만으로 유효한 전화번호라고 판단하면 곤란합니다. 예를 들어 담당자1 010-1234-5678은 숫자만 남겼을 때 101012345678이 됩니다. 이름 뒤의 숫자 1까지 합쳐졌기 때문에 단순 정규화만으로는 잘못된 발송 번호가 만들어질 수 있습니다.
전화번호 컬럼에 설명이 섞일 가능성이 있다면 원본 값, 정규화 결과, 허용 자리수, 업무 규칙을 함께 검사해야 합니다. 아래 예제는 국내 휴대전화 형식을 단순화해 01로 시작하는 10자리 또는 11자리 숫자만 후보로 분류합니다. 실제 서비스에서는 조직의 번호 정책과 국제번호 사용 여부를 먼저 확정해야 합니다.
WITH normalized AS (
SELECT contact_id,
phone_text,
REGEXP_REPLACE(phone_text, '[^0-9]', '') AS phone_digits
FROM contact_sample
)
SELECT contact_id,
phone_text,
phone_digits,
CASE
WHEN REGEXP_LIKE(phone_digits, '^01[0-9]{8,9}$')
THEN '발송 후보'
ELSE '확인 필요'
END AS validation_result
FROM normalized
ORDER BY contact_id;운영 환경 주의
이 정규식은 형식 후보를 가려낼 뿐 번호의 실제 가입 상태나 수신 가능 여부를 확인하지 않습니다. 자동 발송 전에 수신 동의, 중복 번호, 제외 대상, 국제번호와 대표번호 처리 규칙도 별도로 검토해야 합니다.
앞자리 0이 중요한 전화번호는 NUMBER로 바꾸지 않는다
정규화한 전화번호를 TO_NUMBER로 변환하면 01012345678의 앞자리 0이 사라집니다. 전화번호는 계산 대상 숫자가 아니라 숫자로 구성된 식별 문자열입니다. 따라서 VARCHAR2로 유지하는 편이 안전합니다.
SELECT TO_NUMBER('01012345678') AS wrong_for_phone
FROM dual;
-- 결과: 1012345678반대로 금액이나 수량처럼 실제 숫자 변환이 목적이라면 소수점과 음수 부호를 무조건 지워서는 안 됩니다. -12.34에 같은 정규식을 적용하면 1234가 되어 의미가 달라집니다. 이 글의 패턴은 전화번호, 사번, 코드처럼 “숫자 문자만 남기려는 값”에 맞는 방법입니다.
대량 조회에서는 함수 실행 비용과 인덱스를 확인한다
REGEXP_REPLACE는 행마다 정규식 처리를 수행합니다. 소량의 발송 대상 조회에는 편리하지만, 큰 테이블의 조건절에서 컬럼을 함수로 감싸면 기존의 일반 인덱스를 그대로 활용하기 어려울 수 있습니다. 실제 실행계획과 처리 건수를 확인하지 않은 채 전체 고객 테이블에 반복 적용하는 것은 피하는 편이 좋습니다.
정규화 조회가 자주 필요하다면 입력 단계 검증, 별도의 정규화 컬럼, 가상 컬럼, 함수 기반 인덱스 등을 설계 후보로 검토할 수 있습니다. 다만 저장 구조나 인덱스를 바꾸는 작업은 조회 쿼리보다 영향 범위가 큽니다. 중복 번호와 기존 데이터 정비 방식을 합의하고 테스트한 뒤 진행해야 합니다.
조회 단계 보완과 입력 단계 개선을 함께 본다
현재 개발 범위에서 기존 입력 화면까지 고치기 어렵다면 REGEXP_REPLACE로 숫자만 추출하고, 자리수와 시작 번호를 검사해 발송 후보를 만드는 접근은 충분히 현실적입니다. 원본 컬럼은 그대로 두고 정규화 결과를 함께 조회하면 예외 데이터도 추적하기 쉽습니다.
장기적으로는 입력 마스크만 믿기보다 화면의 숫자 제한, 서버 측 유효성 검사, 저장 형식, 기존 데이터 정비를 같은 규칙으로 맞춰야 합니다. 조회 시 정규화는 협업이 완성되기 전의 안전망으로 쓰고, 잘못된 원본까지 정상 번호로 간주하지 않는 것이 경계선입니다.