[ORACLE] 숫자 외의 데이터가 있는 필드 찾기(하이픈 포함 여부)

간혹 날짜나 휴대폰 번호를 문자열 타입으로 넣을 때, 하이픈(-)이 포함되어 있는지를 알고 싶을 때가 있다.

이번에는 휴대폰 번호를 예시로 하이픈(-)이 포함되어 있는 데이터만 조회해보고자 한다.

 

오라클에서 문자열을 치환하고자 할 때 REPLACE 함수를 많이 사용하지만,

위의 경우 TRANSLATE 함수를 이용하면 손쉽게 처리할 수 있다.

 

TRANSLATE는 TRANSLATE ( 문자열, 대상 문자, 변환 문자 ) 형태로 사용할 수 있다.

 

한자리수의 숫자는 0부터 9까지만 존재하므로,

대상문자에 0부터 9까지의 숫자를 넣고 변환문자에 모두 공백만 넣는다면 숫자만 있는 필드는 공백만 리턴될 것이다.

 

이렇게 변환한 필드를 TRIM 처리하게 되면,

숫자만 있는 경우 NULL, 숫자 외에 다른 문자가 섞여 있는 경우 그 외 문자가 리턴될 것이다.

 

그렇다면 조건절에서 NOT NULL인 데이터만 찾으면 숫자만 있는 필드는 제외하고 출력된다.

with example as (
    SELECT '010-0000-0000' AS hp FROM DUAL
    UNION ALL SELECT '01011112222' AS hp FROM DUAL
    UNION ALL SELECT '010-1234-1234' AS hp FROM DUAL
    UNION ALL SELECT '01099995555' AS hp FROM DUAL
)
SELECT
    TRANSLATE (hp, '1234567890', '          ') AS TRANSLATE_DATA,
    hp
FROM
    example
    WHERE TRIM (TRANSLATE (hp, '1234567890', '          ')) IS NOT NULL;

 

결과화면이다.

앞서 말했듯이, 숫자는 모두 공백으로 치환되었다.

 

 

** 내용 추가

정규식을 사용하면 더 쉽고 간결하게 처리가 가능하므로 정규식 사용하는 방법을 추가한다 !!

 

숫자 외에 데이터가 있는 테이블 찾기

with example as (
    SELECT '010-0000-0000' AS hp FROM DUAL
    UNION ALL SELECT '01011112222' AS hp FROM DUAL
    UNION ALL SELECT '010-1234-1234' AS hp FROM DUAL
    UNION ALL SELECT '01099995555' AS hp FROM DUAL
)
SELECT hp
   FROM example
 WHERE REGEXP_LIKE(hp, '[^[:digit:]]')

숫자를 제외한 모든 문자 제거

with example as (
    SELECT '010-0000-0000' AS hp FROM DUAL
    UNION ALL SELECT '01011112222' AS hp FROM DUAL
    UNION ALL SELECT '010-1234-1234' AS hp FROM DUAL
    UNION ALL SELECT '01099995555' AS hp FROM DUAL
)
SELECT REGEXP_REPLACE(hp, '[^[:digit:]]')
   FROM example