오라클 to_number 함수 사용시 ORA-01722 에러 대처방법 몇가지

Oracle to_number( ) 함수는 varchar2 등 문자열 타입에 들어있는 숫자 데이터를 number 타입으로 변환하고자 할 때 사용합니다.

 

그런데, to_number( ) 함수를 사용할때 ORA-01722 에러를 만나는 경우가 꽤 많습니다.

이유는 아래 TEST1 테이블과 같이 데이터에 숫자만 들어있는게 아니라, 특수문자나 알파벳 같은 것들이 들어 있기 때문입니다.

 

SQL> select to_number(data) from TEST1;
ERROR:
ORA-01722: invalid number

 

위의 경우는 TEST1 테이블에 데이터가 몇개 없기 때문에 한번 쭉 조회해보면 쉽게 알 수 있습니다.

하지만, 데이터가 십만건, 백만건 정도 되는 경우는 이 잘못된 데이터를 찾는 것이 쉽지 않습니다.

 

이때, 사용할 수 있는 방법이 오라클에서 제공하는 정규표현식 함수 regexp_like( ) 함수 입니다.

 

SQL> select data
from TEST1
where regexp_like(data, '[^0-9]');

DATA
--------------------
120-
cc123

 

[^0-9] 는 "숫자가 아닌 데이터를 모두 찾아라" 라는 정규표현식(Regulara Expression) 패턴입니다.

이런식으로 잘못된 데이터를 찾아서 해당 데이터를 제대로된 값으로 바꿔주고나서 다시 to_number( ) 를 해주면 되겠습니다.

 

 

또다른 방법으로, 18c New Feature 를 이용하는 방법입니다.

Oracle 18c 부터는 to_number( ) 함수가 기능이 개선되어, 함수안에 on conversion error 라는 키워드를 넣을 수 있습니다. 숫자로 컨버전하다가 에러가 나면 지정한 default 값을 출력하라고 지정하는 것입니다.

 

SQL> select to_number(data default -1 on conversion error)
from   TEST1;

TO_NUMBER(DATADEFAULT-1ONCONVERSIONERROR)
-----------------------------------------
                                     1000
                                    20000
                                      333
                                       -1
                                       -1

 

또 다른 방법으로, validate_conversion( ) 함수를 사용해서 체크해 볼 수 있습니다.

아래처럼 사용하고, "= 0" 대신 "=1" 로 하면 제대로 변환이 가능한 데이터만 조회할 수 있습니다.

 

SQL>
select data
from TEST1
where validate_conversion(data as number) = 0;

DATA
--------------------
120-
cc123

 

on conversion error 와 validate_conversion 함수는 Oracle 18c 의 신기능이기 때문에 18c, 19c 등의 DB에서만 사용할 수 있는 방법입니다.

11g, 12c 등에서는 사용할 수 없습니다.