오라클 문자열에서 숫자만 남기고 다른 문자 제거하기

오라클 데이터베이스에서 전화 번호와 같은 값에서 숫자만 남기고 ), - 등의 다른 문자는 제거하는 방법을 알아 봅니다.

1. 정규식을 사용하는 방법

REGEXP_REPLACE(source_char, pattern) 함수를 사용하는 방법 입니다.

-- 전화번호에서 숫자외의 문자를 제거합니다.
SELECT REGEXP_REPLACE('010-1234-5678', '[^0-9]+') FROM DUAL;

-- 숫자를 자리수로 끊어서 분리합니다.
SELECT REGEXP_REPLACE('01012345678','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})','\1-\2-\3') FROM DUAL;

2. TRANSLATE(expr, from_string, to_string) 함수를 사용하는 방법

SELECT TRANSLATE('010-1234-5678','0'||TRANSLATE('010-1234-5678','x0123456789','x'),'0') FROM DUAL;

TRANSLATE(expr, from_string, to_string) 함수는 expr 문자열에서 from_string내의 첫 번째 문자부터 하나씩 찾아서 발견되면 to_string문자열에서 from_string에서와 같은 위치의 문자로 치환합니다.

to_string의 길이가 짧아서 from_string에서의 위치가 to_string에서 없다면 제거됩니다. expr의 문자가 from_string에 없다면 그대로 가져옵니다.

TRANSLATE('010-1234-5678','x0123456789','x') 을 실행하면 결과는 -- 입니다.

하나씩 알아보면 전화번호에서 'x' 가 있으면 'x' 로 치환됩니다. 0-9까지 문자가 있다면 두 번째 부터는 to_string에 치환할 문자가 없으므로 제거 됩니다. 전화번호의 - 는 from_string에 존재하지 않으므로 그대로 가져옵니다. 

'x'를 사용하는 이유는 to_string에 아무것도 없으면 NULL 이 되어 버리기 때문입니다. 'x'외에 숫자가 아닌 아무 문자나 사용해도 동일한 결과를 얻을 수 있습니다.

이것으로 전화번호에서 제거할 문자를 찾았습니다. 이제 이 문자열을 이용해서 TRANSLATE를 한번 더 사용해서 전화번호에서  숫자를 제외한 문자를 제거합니다.