오라클(Oracle)에서는 ISNUMERIC(숫자확인) 함수가 없다. 직접 사용자함수를 만들어서 사용하거나 제공하는 다른 함수를 사용하여 값이 숫자인지 확인을 할 수 있다.
다음 세 가지 방법을 이용하여 숫자여부를 확인할 수 있다.
방법1. REGEXP_INSTR (정규식 문자열 검색 함수, Oracle 10g 이상 사용가능)
- REGEXP_INSTR([숫자값],'[^0-9]') = 0
- 숫자가 아닌 값의 위치를 반환. 반환 값이 없으면 모든 문자가 숫자임
방법2. TRANSLATE (문자열 치환 함수, Oracle 8i 이상 사용가능)
- TRANSLATE([숫자값],'A1234567890','A') IS NULL
- 숫자를 찾아 치환 후 남은 문자가 존재 시 숫자가 아님
방법3. 사용자 정의 함수 생성 (IS_NUMBER)
- IS_NUMBER([숫자값]) = 1
- 숫자면 1, 문자면 0을 반환함
숫자값이 입력되는 문자(VARCHAR) 컬럼의 값을 연산을 해야하는 경우 숫자가 아닌 문자가 포함되어 있으면 오류가 발생한다.
방법1. REGEXP_INSTR (정규식 문자열 검색 함수, Oracle 10g 이상 사용가능)
WITH TEST_TABLE 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) PRICE
FROM TEST_TABLE
WHERE REGEXP_INSTR(PRICE,'[^0-9]') = 0
방법2. TRANSLATE (문자열 치환 함수, Oracle 8i 이상 사용가능)
WITH TEST_TABLE 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) PRICE
FROM TEST_TABLE
WHERE TRANSLATE(PRICE,'A1234567890','A') IS NULL
방법3. 사용자 정의 함수 생성 (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 ;
WITH TEST_TABLE 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) PRICE
FROM TEST_TABLE
WHERE IS_NUMBER(PRICE) = 1
숫자를 문자로 변환 시 소수점 처리 (TO_CHAR, FM)
동적쿼리(Dynamic SQL) 사용법 (텍스트 쿼리)