[DATABASE/ORACLE] 숫자 체크하기

■ 숫자를 체크하는 방법을 보여준다.

 

▶ REGEXP_INSTR 함수 사용하기 ; Oracle 10g 이상 버전부터 사용 가능하다. (SQL)

WITH TEST_TABLE AS
(
    SELECT '신라면' PRODUCT, '650'  PRICE FROM DUAL UNION ALL
    SELECT '너구리' PRODUCT, '700'  PRICE FROM DUAL UNION ALL
    SELECT '진라면' PRODUCT, '$460' PRICE FROM DUAL
)
SELECT
    PRODUCT
   ,TO_NUMBER(PRICE) PRICE
FROM  TEST_TABLE
WHERE REGEXP_INSTR(PRICE, '[^0-9]') = 0; -- 정수만 체크하는 경우

 

▶ REGEXP_INSTR 함수를 사용해 부호(+, -), 소수점까지 체크하는 경우 (SQL)

WITH TEST_TABLE AS
(
    SELECT '신라면' PRODUCT, '650'  PRICE FROM DUAL UNION ALL
    SELECT '너구리' PRODUCT, '700'  PRICE FROM DUAL UNION ALL
    SELECT '진라면' PRODUCT, '$460' PRICE FROM DUAL
)
SELECT
    PRODUCT
   ,TO_NUMBER(PRICE) PRICE
FROM  TEST_TABLE
WHERE REGEXP_INSTR(PRICE, '^[+-]?\d*(\.?\d*)$') = 1;

 

▶ TRANSLATE 함수 사용하기 ; Oracle 8i 이상 버전에서 사용 가능하다. (SQL)

WITH TEST_TABLE AS
(
    SELECT '신라면' PRODUCT, '650'  PRICE FROM DUAL UNION ALL
    SELECT '너구리' PRODUCT, '700'  PRICE FROM DUAL UNION ALL
    SELECT '진라면' PRODUCT, '$460' PRICE FROM DUAL
)
SELECT
    PRODUCT
   ,TO_NUMBER(PRICE) PRICE
FROM  TEST_TABLE
WHERE TRANSLATE(PRICE, 'A1234567890', 'A') IS NULL;

 

▶ 사용자 정의 함수 사용하기 (IS_NUMBER) (SQL)

CREATE OR REPLACE FUNCTION IS_NUMBER(P_SOURCE VARCHAR2) RETURN NUMBER
IS
    V_RESULT NUMBER;
BEGIN
    IF P_SOURCE IS NULL OR LENGTH(TRIM(P_SOURCE)) = 0 THEN
        RETURN 0;
    END IF;

    V_RESULT := TO_NUMBER(P_SOURCE);

    RETURN 1;

EXCEPTION WHEN OTHERS THEN

    RETURN 0;
END;

WITH TEST_TABLE AS
(
    SELECT '신라면' PRODUCT, '650'  PRICE FROM DUAL UNION ALL
    SELECT '너구리' PRODUCT, '700'  PRICE FROM DUAL UNION ALL
    SELECT '진라면' PRODUCT, '$460' PRICE FROM DUAL
)
SELECT
    PRODUCT
   ,TO_NUMBER(PRICE) PRICE
FROM  TEST_TABLE
WHERE IS_NUMBER(PRICE) = 1;