TO_NUMBER() 안먹힐때 in Oracle

Oracle에서 TO_NUMBER() 함수가 안먹히는 경우에 컬럼이 숫자로만 이루어져 있는지 확인해야 한다.

 

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

 

SELECT 

      CASE

            WHEN

                      TRANSLATE('-1234.56','A1234567890.+-','A') IS NULL

            THEN
                      TO_NUMBER('-1234.56')
       END
  FROM dual

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

SELECT 

     CASE

         WHEN

                   REGEXP_INSTR('-1234.56','^[+-]?\d*(\.?\d*)$') = 1

         THEN
                   TO_NUMBER('-1234.56')
       END
  FROM dual


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

SELECT 

      CASE 

            WHEN 

                  VALIDATE_CONVERSION('-1234.56' AS NUMBER) = 1 

            THEN
                 TO_NUMBER('-1234.56')
       END
  FROM dual
 

4. 오라클 12c부터 정식 형변환 체크 함수가 생겼다.

SELECT 

       TO_NUMBER('123456' DEFAULT -1 ON CONVERSION ERROR)   AS result1
     , TO_NUMBER('$123456' DEFAULT -1 ON CONVERSION ERROR)   AS result2
     , CAST('123456' AS NUMBER(10) DEFAULT -1 ON CONVERSION ERROR)   AS result3
  FROM dual