[Oracle] 문자열에서 숫자만 추출 3가지 방법

오라클에서 날짜, 전화번호와 같은 문자열에서 특수문자 또는 문자를 제거하고 숫자만 추출해야 할 경우가 있다. 문자열에서 숫자만 추출하기 위해서는 Oracle 10g부터 추가된 정규식 함수를 사용하면 쉽게 해결이 된다. Oracle 10g 이전 버전에서는 TRANSLATE 함수를 사용하여 문자열에서 숫자만 추출할 수 있다. 정규식 함수를 사용하면 문자열에서 원하는 위치의 숫자만 추출할 수 있기 때문에 조금 더 다양한 결과를 얻을 수 있다.

 

오라클 문자열에서 숫자 추출 방법

목차 REGEXP_REPLACE 함수를 사용하여 숫자만 추출 (Oracle 10g 이상) TRANSLATE 함수를 사용하여 숫자만 추출 (Oracle 8i 이상) 문자열의 마지막 숫자만 추출 (REGEXP_SUBSTR 함수)

 

REGEXP_REPLACE 함수를 사용하여 숫자만 추출 (Oracle 10g 이상)

전화번호에서 숫자만 추출

WITH temp AS (
    SELECT '010-1234-5678' AS tel_no FROM dual UNION ALL
    SELECT '010.1234.1234' AS tel_no FROM dual
)

SELECT tel_no
     , REGEXP_REPLACE(tel_no, '[^0-9]') AS new_tel_no
  FROM temp

 

 

전화번호에서 숫자가 아닌 특수문자를 제거하고 숫자만 반환한다.

 

[0-9] : 숫자

[^0-9] : 숫자가 아닌 것

 

날짜에서 숫자만 추출

WITH temp AS (
    SELECT '2022-09-20' AS dte FROM dual UNION ALL
    SELECT '2022/09/20' AS dte FROM dual UNION ALL
    SELECT '2022년09월20일' AS dte FROM dual
)

SELECT dte
     , REGEXP_REPLACE(dte, '[^0-9]') AS new_dte
  FROM temp

 

 

날짜에서 숫자가 아닌 문자를 제거 후 숫자만 반환한다.

 

TRANSLATE 함수를 사용하여 숫자만 추출 (Oracle 8i 이상)

WITH temp AS (
    SELECT '2022-09-20' AS dte FROM dual UNION ALL
    SELECT '2022/09/20' AS dte FROM dual UNION ALL
    SELECT '2022년09월20일' AS dte FROM dual
)

SELECT dte
     , TRANSLATE(dte, '0123456789' || dte, '0123456789') AS new_dte
  FROM temp

 

 

TRANSLATE 함수는 특정 문자를 지정된 문자로 치환하는 함수이다. 숫자만 치환되도록 지정하였으며, 나머지 문자는 치환되지 못하기 때문에 제거된다.

 

TRANSLATE 함수의 자세한 사용법은 아래를 참고하면 된다.

[Oracle] 오라클 TRANSLATE 함수 사용법

 

문자열의 마지막 숫자만 추출 (REGEXP_SUBSTR 함수)

WITH temp AS (
    SELECT '강남구 삼성1동 영동대로 517' AS addr FROM dual
)

SELECT addr
     , REGEXP_SUBSTR(addr, '[0-9]+$') AS new_addr
  FROM temp

 

 

문자열에서 숫자만 추출할 때 특정 위치의 숫자만 추출해야 할 경우가 있다.

위의 예제는 문자열의 마지막에 숫자가 존재할 경우 해당 숫자를 추출하는 예제이다.

 

REGEXP_SUBSTR 함수는 Oracle 10g부터 사용할 수 있다.