오라클에서 문자열을 숫자형으로 변환하기 위해서는 TO_NUMBER 함수와 CAST 함수를 사용할 수 있다. 문자열에 수치 값 외에 문자가 포함되어 있으면 형 변환 시 오류가 발생하는데, 오류를 미리 예방하기 위해서 다양한 방법을 사용할 수 있으며 자주 사용하는 방법 몇 가지를 설명해 두었으니 아래를 참고하면 된다.
문자열 숫자 변환
1. TO_NUMBER 함수
SELECT TO_NUMBER('12345')
, TO_NUMBER('12345.67')
FROM dual
 |
TO_NUMBER 함수를 사용하면 문자열을 쉽게 숫자(정수, 실수)로 변환이 가능하다.
2. CAST 함수
SELECT CAST('12345' AS NUMBER(10))
, CAST('12345.56' AS NUMBER(10))
, CAST('12345.56' AS NUMBER(10,2))
FROM dual
 |
CAST 함수를 사용하면 숫자의 크기(자릿수)를 정의하여 변환이 가능하며, 변환하려고 하는 수치 값의 자릿수보다 정의된 자릿수가 작으면 오류가 발생한다.
소수점 이하의 자리수는 자릿수가 맞지 않아도 오류가 발생하지 않으며, 정의된 자릿수 이하의 값은 반올림되어서 변환된다. (예시 쿼리의 두 번째 변환 반올림 부분 확인)
문자열 숫자 변환 시 오류 처리
 |
문자열에 변환이 불가능한 문자가 포함되어 있으면 변환 시 오류가 발생한다. (+-. 가능)
ORA-01722: 수치가 부적합합니다
아래의 3가지 방법으로 오류를 미리 예방할 수 있다.
|
1. 숫자 외에 문자를 없애는 방법
SELECT REPLACE(REPLACE('$123,456', ','), '$') AS result1
, REGEXP_REPLACE('$123,456', '[^0-9]', '') AS result2
, REGEXP_REPLACE('num -123.456', '[^0-9.-]', '') AS result3
FROM dual
 |
REPLACE 함수 또는 REGEXP_REPLACE 함수를 사용하여 숫자 외에 문자를 모두 제거하고 TO_NUMBER, CAST 함수를 사용하여 숫자로 변환하면 오류를 미리 예방할 수 있다.
REGEXP_REPLACE 함수는 오라클 10g 이상부터 사용 가능하다.
2. 숫자 외에 문자가 포함되었는지 미리 체크
SELECT CASE WHEN TRANSLATE('-1234.56','A1234567890.+-','A') IS NULL THEN
TO_NUMBER('-1234.56')
END
FROM dual
TRANSLATE 함수를 사용하여 숫자 체크 (오라클 8i 이상)
SELECT CASE WHEN REGEXP_INSTR('-1234.56','^[+-]?\d*(\.?\d*)$') = 1 THEN
TO_NUMBER('-1234.56')
END
FROM dual
REGEXP_INSTR 함수를 사용하여 숫자 체크 (오라클 10g 이상)
SELECT CASE WHEN VALIDATE_CONVERSION('-1234.56' AS NUMBER) = 1 THEN
TO_NUMBER('-1234.56')
END
FROM dual
VALIDATE_CONVERSION 함수를 사용하여 숫자 체크 (오라클 12c R2 이상)
오라클 12c부터 정식 형 변환 체크 함수가 생겼다.
3. 숫자로 변환 시 오류가 발생하면 대체 값 지정
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
 |
오라클 12c R2부터 ON CONVERSION ERROR 구문을 사용하여 형 변환 시 오류가 발생하면 대체 값을 출력할 수 있다. DEFAULT 뒷부분에 대체 값을 지정하면 된다.