[ORACLE] TO_NUMBER() 함수로 문자형 숫자 데이터 안전하게 변환하는 방법

[Oracle] TO_NUMBER

1. TO_NUMBER 함수란?

Oracle의 TO_NUMBER() 함수는 문자열(String)이나 날짜(Date) 데이터를 숫자(Number)로 변환할 때 사용하는 변환 함수입니다. 주로 사용자 입력 데이터나 외부 파일 데이터를 정형화된 숫자로 변환할 때 유용합니다.

2. 기본 문법

TO_NUMBER(char [, format_model] [, nls_parameter])

• char: 변환할 문자형 데이터
• format_model: (선택) 변환할 숫자 형식
• nls_parameter: (선택) 지역화 설정

3. 기본 사용 예제

SELECT TO_NUMBER('12345') AS result FROM dual; -- 결과: 12345

위 예제는 문자열 '12345'를 숫자 12345로 변환합니다.

4. 포맷 모델을 사용하는 예제

SELECT TO_NUMBER('12,345.67', '99,999.99') AS result FROM dual; -- 결과: 12345.67

포맷 모델을 지정하여 천 단위 구분기호와 소수점을 인식하도록 합니다.

5. 날짜 값을 숫자로 변환

SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')) AS today_num FROM dual;

날짜를 문자열로 바꾼 후 숫자로 변환하는 방식입니다. 날짜 데이터를 정렬 또는 비교 목적으로 사용할 때 활용됩니다.

6. 잘못된 입력 처리

SELECT TO_NUMBER('abc') FROM dual; -- ORA-01722: invalid number 오류 발생

TO_NUMBER()는 변환이 불가능한 문자열에 대해 오류를 반환합니다. CASE WHEN이나 REGEXP를 활용한 사전 검증이 필요할 수 있습니다.

7. 실무 활용 팁

• ETL 처리 시 필수 함수: 외부에서 입력된 문자열 숫자 필드를 정수/실수로 정규화할 때 사용
• 숫자 정렬 처리: 문자열 정렬과 숫자 정렬의 차이 극복
• 금액 데이터 처리: 천 단위 구분 쉼표 포함된 금액 데이터 변환

8. 숫자 형식(format_model) 주요 구성 요소

형식	설명	예시
9	숫자 자리 표시	'999' → 123
0	숫자 자리 표시 (0을 채움)	'000' → 007
, (콤마)	천 단위 구분	'9,999' → 1,234
.	소수점 구분	'999.99' → 123.45
FM	포맷 내 불필요한 공백 제거	'FM999' → '123'

9. 자주 발생하는 오류 및 해결법

• ORA-01722: invalid number: 변환 대상 문자열이 숫자 형식이 아닐 때 발생. 사전 정규표현식 검사 권장.
• 포맷 모델 오류: 입력값과 format_model이 일치하지 않으면 변환 실패.

10. 응용 예제

-- 금액 문자열을 숫자로 변환 후 누적합 계산
SELECT
  SUM(TO_NUMBER(REPLACE(amount, ',', ''))) AS total_amount
FROM sales_data;

천 단위 콤마를 제거한 후 TO_NUMBER()로 변환하여 합계를 계산하는 예시입니다.

11. 결론

TO_NUMBER() 함수는 Oracle에서 데이터 변환 및 정규화의 핵심 도구입니다. 포맷 모델을 적절히 활용하면 다양한 형태의 문자열 데이터를 안정적으로 숫자로 변환할 수 있습니다. 실무에 적용 시 사전 데이터 검증과 예외 처리 로직을 함께 고려하는 것이 중요합니다.

12. 참고 자료

• Oracle 공식 문서
• Oracle Tutorial - TO_NUMBER()
• SQL Tutorial - Oracle TO_NUMBER