변환함수 - TO_CHAR, TO_DATE, TO_NUMBER

 

변환함수에는 TO_CHAR, TO_DATE, TO_NUMBER 이 대표적으로 쓰입니다.

 

TO_CHAR

숫자나 날짜 데이터 유형을 문자열로 변환하는 데 사용됩니다.

이 함수는 다양한 형식(format) 을 적용할 수 있어 출력 결과를 원하는 형식으로 쉽게 조작할 수 있습니다.

TO_CHAR(value, [format], [nls_params])

value

변환하려는 값을 넣습니다.(숫자 또는 날짜 데이터 유형)

format

변환된 문자열의 형식을 지정합니다.

nls_params

선택사항으로 숫자와 날짜 형식의 언어적 차이를 지정합니다.

예제

SELECT TO_CHAR('123456', '999,999') AS format_number,
	   TO_CHAR('123456', 'L999,999MI') AS format_number2
   FROM DUAL;

format_number : 123,456
format_number2 : ₩123,456-

SELECT TO_CHAR(1234567.89, '9,999,999.99') AS formatted_amount FROM DUAL;

결과: 1,234,567.89

 

중요한 점은 입력된 value 에 맞게 '9,999,9' 를 설정하지 않으셔도 됩니다. FM 이라는 기능이 있기 때문입니다.

FM 은 문자열의 공백을 제거합니다. 또한 9의 값은 값이 없으면 표시하지 않고, 0은 값이 없으면 "0"으로 처리해줍니다.

SELECT TO_CHAR(123467, 'FM999,999')        --123,467
     , TO_CHAR(123467890, 'FM999,999,999') --123,467,890
     , TO_CHAR(123467, 'FML999,999')       --￦123,467
  FROM dual

SELECT TO_CHAR(123)            --123
     , TO_CHAR(123, 'FM00000') --00123
  FROM dual

 

TO_CHAR 을 이용하여 임의의 구분자로 날짜 형식을 만들어 줄 수도 있습니다.

SELECT TO_CHAR(SYSDATE, '""YYYY"년 "MM"월 "DD"일"') --2020년 07월 23일
     , TO_CHAR(SYSDATE, '""HH24"시 "MI"분 "SS"초"') --11시 12분 20초
  FROM dual

 

 

TO_DATE

오라클에서 문자열을 날짜형 데이터로 형 변환을 하기 위해서는 TO_DATE 함수를 사용하면 됩니다.

TO_DATE("문자열", "날짜 포맷")

 

오라클에서는 날짜 포맷의 대소문자를 구분하지 않습니다.

즉 YYYY-MM-DD HH24:MI:SS 를 yyyy-mm-dd hh24:mi:ss 로 변경해도 정상적으로 작동된다는 말입니다.

SELECT TO_DATE('2021-12-12', 'YYYY-MM-DD')
     , TO_DATE('2021-12-12 17:10:00', 'YYYY-MM-DD HH24:MI:SS')
  FROM dual

 

또한 날짜의 구분자를 사용하지 않고 사용하거나 년/월/일의 위치를 변경하여 포맷을 정의할 수도 있습니다.

SELECT TO_DATE('20211212', 'YYYYMMDD')
     , TO_DATE('20211212171000', 'YYYYMMDDHH24MISS')
  FROM dual

SELECT TO_DATE('2021/12/12', 'YYYY/MM/DD')
     , TO_DATE('12/12/2021', 'MM/DD/YYYY')
  FROM dual

SELECT TO_DATE('19/12/11', 'RR/MM/DD')
     , TO_DATE('21/12/12', 'RR/MM/DD')
  FROM dual

 

TO_NUMBER

오라클에서 문자열을 숫자형 데이터로 형 변환을 하기 위해서는 TO_NUMBER 함수를 사용하면 됩니다.

TO_NUMBER('12345')

 

문자열을 숫자로 변환하려면 아래처럼 간단한 코드로 변경하실 수 있습니다.

SELECT TO_NUMBER('12345')
     , TO_NUMBER('12345.67')
  FROM dual

 

만약 문자열에 변환이 불가능한 문자가 포함되어 있으면 변환이 가능하지 않습니다.

ORA-01722: invalid number 오류가 발생합니다.

 

따라서 아래의 3가지 방법으로 오류를 미리 예방할 수 있습니다.

숫자 외에 문자를 없애는 방법
숫자 외에 문자가 포함되었는지 미리 체크
숫자로 변환 시 오류가 발생하면 대체 값 지정 ( oracle 12c R2 이상 )

 

아래는 정규 표현식을 사용하여 오류를 방지하는 방법 입니다.

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

 

숫자로 변환 시 오류가 발생한다면 대체 값을 지정하고 싶을 때는 ON CONVERSION ERROR 구문을 사용하여 형 변환 시 오류가 발생하면 대체 값을 출력할 수 있습니다.

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