[Oracle] 오라클 숫자 체크 방법 (IS_NUMBER, IS_NUMERIC)

오라클 SQL에서는 기본 숫자 체크 함수(ISNUMERIC)를 오랫동안 제공하지 않았기 때문에 다양한 숫자 체크 방법을 사용해 왔다. 그러나 오라클 12c R2 버전부터 VALIDATE_CONVERSION 함수가 제공되기 때문에 숫자 여부를 쉽게 체크할 수 있다. 그러나 이전 오라클 버전에서는 숫자를 체크하는 공식 함수가 없기 때문에 정규식 함수나 TRANSLATE 함수를 사용하여 숫자 여부를 체크하면 된다.

 

목차 VALIDATE_CONVERSION 함수를 사용하여 숫자 체크 (오라클 12c R2 이상) REGEXP_INSTR 정규식 함수를 사용하여 숫자 체크 (오라클 10g 이상) TRANSLATE 함수를 사용하여 숫자 체크 (오라클 8i 이상) 사용자 정의 함수 생성하여 숫자 체크 (IS_NUMBER)

 

VALIDATE_CONVERSION 함수를 사용하여 숫자 체크 (오라클 12c R2 이상)

WITH temp AS (
   SELECT 'SHIN RAMYUN' product, '650'   price FROM dual UNION ALL
   SELECT 'NEOGURI'     product, '700'   price FROM dual UNION ALL
   SELECT 'JIN RAMEN'   product, '￦460' price FROM dual
)

SELECT product
     , TO_NUMBER(price) AS price
  FROM temp
 WHERE VALIDATE_CONVERSION(price AS NUMBER) = 1

 

 

오라클 12c R2 버전부터 VALIDATE_CONVERSION 함수가 제공되기 때문에 쉽게 숫자를 체크할 수 있다.

공식 함수이기 때문에 부호가 있는 숫자, 소수점이 있는 숫자 등 모두 체크가 가능하다.

 

VALIDATE_CONVERSION 함수는 숫자 외에도 다양한 데이터 형식을 체크할 수 있다.

 

REGEXP_INSTR 정규식 함수를 사용하여 숫자 체크 (오라클 10g 이상)

WITH temp AS (
   SELECT 'SHIN RAMYUN' product, '650'   price FROM dual UNION ALL
   SELECT 'NEOGURI'     product, '700'   price FROM dual UNION ALL
   SELECT 'JIN RAMEN'   product, '￦460' price FROM dual
)

-- 정수만 체크가능 
SELECT product
     , TO_NUMBER(price) AS price
  FROM temp
 WHERE REGEXP_INSTR(price,'^[0-9]+$') = 1

 

 

정규식 함수(REGEXP_INSTR)는 정규식을 사용하여 문자열의 다양한 패턴을 검색할 수 있다.

 

위의 예제는 시작과 끝까지 모두 숫자인 경우를 찾는 방법이다. 위의 패턴의 단점을 NULL인 값은 체크하지 못한다.

NULL도 숫자로 판단하기 위해서는 추가로 조건을 추가해 줘야 한다.

 

REGEXP_INSTR(price,'^[0-9]+$') = 1 패턴 말고도

REGEXP_INSTR(price,'[^0-9]') = 0 패턴을 사용해도 동일한 결과를 얻을 수 있다.

 

-- 부호(+,-), 소수점까지 체크가능
SELECT product
     , TO_NUMBER(price) AS price
  FROM temp
 WHERE REGEXP_INSTR(price,'^[+-]?\d*(\.?\d*)$') = 1

 

위의 정규식 패턴을 사용하면 부호(+,-)가 있는 숫자와 소수점이 있는 숫자까지 체크할 수 있다.

 

TRANSLATE 함수를 사용하여 숫자 체크 (오라클 8i 이상)

WITH temp AS (
   SELECT 'SHIN RAMYUN' product, '650'   price FROM dual UNION ALL
   SELECT 'NEOGURI'     product, '700'   price FROM dual UNION ALL
   SELECT 'JIN RAMEN'   product, '￦460' price FROM dual
)

SELECT product
     , TO_NUMBER(price) AS price
  FROM temp
 WHERE TRANSLATE(price,'A1234567890','A') IS NULL

 

 

위의 예제는 TRANSLATE 함수를 사용하여 문자열의 숫자를 모두 NULL로 치환하여, 문자열 전체가 NULL인 경우 숫자로 판단하는 예제이다.

 

TRANSLATE 함수의 사용법이 조금 어렵게 느껴질 수 있기 때문에 하단의 포스팅을 참고하면 쉽게 이해할 수 있다.

 

사용자 정의 함수 생성하여 숫자 체크 (IS_NUMBER)

-- 사용자 정의 함수 생성
CREATE OR REPLACE FUNCTION IS_NUMBER(str VARCHAR2) RETURN NUMBER
IS
   v_ret NUMBER;
BEGIN

   IF str IS NULL OR LENGTH(TRIM(str)) = 0 THEN
      RETURN 0;
   END IF;

   v_ret := TO_NUMBER(str);

   RETURN 1;

   EXCEPTION WHEN OTHERS THEN
      RETURN 0;

END;

 

위의 사용자 정의 함수(IS_NUMBER)를 컴파일 후 숫자 여부를 체크해야 할 때 사용하면 된다.

 

WITH temp AS (
   SELECT 'SHIN RAMYUN' product, '650'   price FROM dual UNION ALL
   SELECT 'NEOGURI'     product, '700'   price FROM dual UNION ALL
   SELECT 'JIN RAMEN'   product, '￦460' price FROM dual
)

SELECT product
     , TO_NUMBER(price) AS price
  FROM temp
 WHERE IS_NUMBER(price) = 1

 

 

오라클에서 숫자 체크 함수를 제공하지 않았기 때문에 오랫동안 사용자 정의 함수를 생성해서 많이 사용해 왔다.

 

현재는 공식함수가 생겼기 때문에 공식 함수를 사용하는 것이 좋을 듯하며, 하위 오라클 버전에서는 아직 위의 방법을 유용하게 사용할 수 있을 듯하다.

 

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

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