[오라클 에러] 오라클 ORA-01722 : Invalid Number 에러 해결 방법

 

안녕하세요.

 

오늘은 오라클에서 ORA-01722 : Invalid Number 에러 해결 방법에 대해서 알려드리고자 합니다.

 

얼마전에 회사에서 일을 하면서 특정 테이블의 데이터를 조회를 하려고 SQL문을 작성했는데.. ORA-01722 : Invalid Number 에러를 만나게 되었습니다.

 

이것을 해결하려고 약 2시간은 혼자서 계속 삽질을 했던 것 같은데요..해결을 해서 같이 공유를 해 드리고자 합니다.

 

저 같은 경우는 숫자를 사용하는 컬럼의 데이터 타입이 VARCHAR2 형식으로 문자 형식으로 선언되었고, 해당 컬럼 안에 공백과 ‘*’ (에스터리스크)와 같은 잘못된 문자들이 들어 있어서 문제가 발생 하였습니다.

 

제가 직접 위의 상황을 재연해 보았습니다.

 

먼저 아래 STUDENT 테이블이 있습니다.

여기서 SCORE 컬럼을 보시게 되면 맨 아래 이름이 “이우동”, “이이정” 이 두 학생의 SCORE 컬럼에 제가 임의로 ‘*’ 문자와 NULL 값을 집어 넣어서 데이터를 저장 하였습니다.

 

그리고 아래와 같이 성적이 45점 이상 80점 이하인 학생을 조회하는 쿼리를 작성하고 직접 실행을 시켜 보았습니다.

 

SQL 쿼리

1 2 3 4

SELECT SCHOOL_NO, CLASS, NAME, AGE, GRADE, SCORE FROM WIN.STUDENT WHERE 1 =1  AND TO_NUMBER(SCORE) BETWEEN 45 AND 80

cs

 

실행 결과

위와 같이 Invalid Number 에러가 발생 했습니다.

 

문제는 바로 SCORE 컬럼에 현재 ‘*’ 문자와 NULL값이 함께 저장 되어있기 때문에 TO_NUMBER(SCORE) 로 NUBER 타입으로 형 변환을 해도 위와 같은 에러가 발생하게 되는 것입니다.

그럼 이제 위의 에러를 해결하는 쿼리를 작성해 보도록 하겠습니다.

 

SQL 쿼리

1 2 3 4

SELECT SCHOOL_NO, CLASS, NAME, AGE, GRADE, SCORE FROM WIN.STUDENT WHERE 1 =1  AND TO_NUMBER(REPLACE(NVL(SCORE, '0'), '*', '0')) BETWEEN 45 AND 80

cs

 

실행 결과

위와 같이 에러 없이 제대로 데이터가 조회된 것을 확인하실 수 있습니다.

 

제가 선택한 방법은 NVL함수를 이용하여 먼저 NULL값이 있으면 ‘0’ 으로 

변환해주고, 그 다음으로 REPLACE 함수를 이용하여 ‘*’ 문자 또한 ‘0’으로 변환

 시켜주도록 쿼리를 바꿔 주었습니다.

 

그랬더니 문제 없이 쿼리가 정상적으로 실행 되었습니다~~

 

혹시 저와 같은 에러를 만나게 되신다면, 해당 컬럼의 타입을 확인하시고 해당 컬럼 데이터를 전체 조회해서 포맷에 맞지 않는 문자 혹은 NULL값이 있는지를 체크하여 쿼리를 작성하시게 되면 위와 같은 에러는 만나지 않으실거에요~~

 

감사합니다.^^