(Oracle) TO_CHAR, TO_NUMBER, TO_DATE 함수 정리

오라클(Oracle) TO_CHAR, TO_NUMBER, TO_DATE 함수 정리

 

TO_CHAR, TO_NUMBER, TO_DATE

최근에 오라클을 조금 사용해 보게 되면서 그 과정에서 알게 된 'TO_CHAR', 'TO_NUMBER', 'TO_DATE' 함수에 대해 정리한 내용입니다.

---

TO_CHAR

먼저 'TO_CHAR' 함수는 날짜, 숫자 등의 값을 문자열로 변환하는 함수입니다.

해당 함수의 다양한 변환 방법의 예시는 아래와 같습니다.

 

1. 날짜 관련

-- 날짜 포맷 변경1
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')              -- 20231216
     , TO_CHAR(SYSDATE, 'YYYY/MM/DD')            -- 2023/12/16
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD')            -- 2023-12-16
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') -- 2023-12-16 04:54:57
FROM dual

(날짜 포맷 변경 관련 예시 1)

 

-- 날짜 포맷 변경2
SELECT TO_CHAR(SYSDATE, '""YYYY"년 "MM"월 "DD"일"') -- 2023년 12월 16일
     , TO_CHAR(SYSDATE, '""HH24"시 "MI"분 "SS"초"') -- 11시 43분 01초
FROM dual

(날짜 포맷 변경 관련 예시 2)

 

-- 날짜 포맷 변경3 (오전, 오후)
SELECT TO_CHAR(SYSDATE, 'AM')                     -- AM
     , TO_CHAR(SYSDATE, 'AM HH:MI:SS')            -- AM 11:44:31
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD AM HH:MI:SS') -- 2020-07-23 AM 11:44:31
FROM dual

(날짜 포맷 변경 관련 예시 3)

 

* YYYY: 년, YY: 년, MM: 월, DD: 일, HH24: 24시간, HH: 12시간, MI: 분, SS:초

 

-- 1년 기준 일, 주, 분기 (조회 기준 날짜 2023-12-16)
SELECT TO_CHAR(SYSDATE, 'DDD') -- 350 (365일 기준)
     , TO_CHAR(SYSDATE, 'WW')  -- 50 (1년 기준)
     , TO_CHAR(SYSDATE, 'Q')   -- 4 (4분기)
FROM dual

 

 

 

2. 숫자 관련

Oracle에서는 숫자를 문자로 변환할 때 첫 번째 자리에 '0'이 있을 경우 '0'이 생략되어 결과가 표출되는 경우가 있습니다.

 

'FM', '9', '0'

아래 변환 형식에서 사용되는 'FM'의 경우 좌우 공백을 제거하는 것이며, '9'는 가변적인 값으로 0이거나 숫자가 없을 시 값을 버립니다.

'0'은 고정적인 값으로 변환된 숫자의 길이를 맞추고 싶을 때 원하는 길이만큼을 0으로 채워주면 됩니다.

 

* 이때 정수는 지정한 형식보다 값의 길이가 길면 오류가 발생하고, 소수의 경우 지정한 길이보다 길면 반올림이 됩니다.

 

SELECT TO_CHAR(1234.5678, 'FM990.9999') -- ######### (정수 부분의 값이 지정된 형식보다 길기 때문에 오류 발생)
     , TO_CHAR(1234.56, 'FM9990.99')    -- 1234.56
     , TO_CHAR(0.1234, 'FM9.9999')      -- .1234 (0이 사라지게 됨)
     , TO_CHAR(0.1234, 'FM0.9999')      -- 0.1234
     , TO_CHAR(0.12345, 'FM0.9999')     -- 0.1235 (소수점 반올림)
     , TO_CHAR(0.12345, 'FM0.999')      -- 0.123
FROM dual

(숫자 변경 관련 예시 1)

 

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

(숫자 변경 관련 예시 2)

0으로 지정한 자릿수를 맞춰야 하는 경우 다음과 같이 사용될 수 있습니다.

 

SELECT TO_CHAR(1234567, 'FM999,999,999')   -- 123,467
     , TO_CHAR(123456789, 'FM999,999,999') -- 123,467,890
     , TO_CHAR(123456789, 'FM999,999')     -- ######## (값이 지정된 형식보다 긴 경우 오류 발생)
FROM dual

(숫자 변경 관련 예시 3)

숫자에 콤마(,)를 표현해서 출력하고 싶은 경우 다음과 같이 사용될 수 있습니다.

 

---

TO_NUMBER

이어서 'TO_NUMBER' 함수입니다. 해당 함수를 사용하면 문자열을 숫자(정수, 실수)로 변환할 수 있습니다.

다만 문자열에 변환이 불가능한 문자가 포함되어 있을 경우 오류가 발생합니다.

ORA_01722: invalid number

 

(문자열 맨 앞에 '+' 또는 '-'가 붙은 경우는 변환이 가능합니다.)

 

SELECT TO_NUMBER('12345')    -- 12345
     , TO_NUMBER('12345.67') -- 12345.67
     , TO_NUMBER('+123')     -- 123
     , TO_NUMBER('-123')     -- -123
FROM dual

(숫자 변경 관련 예시 1)

 

SELECT TO_NUMBER('123ab5' default -1 ON CONVERSION ERROR)    -- -1
     , TO_NUMBER('123cd5.67' default -1 ON CONVERSION ERROR) -- -1
FROM dual

(숫자 변경 관련 예시 2)

TO_NUMBER 함수를 통해 문자열을 숫자로 변환하는 과정에서 오류가 발생할 경우 위 예시와 같이 'ON CONVERSION ERROR'를 통해 원하는 default 값으로 결과를 출력할 수 있습니다.

 

 

---

TO_DATE

문자 데이터를 날짜 데이터 타입으로 변환해 주는 함수인 'TO_DATE'입니다.

해당 함수의 사용에서 중요한 부분은 '문자열의 데이터 형식이 변환하려는 날짜 포맷과 같은' 형식이어야 한다는 것입니다.

만약 형식이 일치하지 않은 경우 오류가 발생합니다.

 

SELECT TO_DATE('2023-12-16', 'YYYY-MM-DD')                     -- 2023-12-16 00:00:00
     , TO_DATE('2023-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS') -- 2023-12-16 18:20:00 
     , TO_DATE('20231216', 'YYYYMMDD')                         -- 2023-12-16 00:00:00
     , TO_DATE('20231216182000', 'YYYYMMDDHH24MISS')           -- 2023-12-16 18:20:00
     , TO_DATE('2023/12/16', 'YYYY/MM/DD')                     -- 2023-12-16 00:00:00
     , TO_DATE('12/16/2023', 'MM/DD/YYYY')                     -- 2023-12-16 00:00:00
FROM dual

(날짜 데이터 타입으로 변환 예시 1)

 

* 이때 출력되는 결과는 oracle에 설정된 날짜 포맷에 따라 다를 수 있습니다.

 

 

---

같이 사용되는 예시

SELECT TO_CHAR(TO_DATE('20231216', 'YYYY-MM-DD'), 'YYYY/MM/DD') -- 2023/12/16
     , TO_NUMBER(TO_CHAR(SYSDATE, 'MM'))                        -- 12       
FROM dual

(해당 함수가 중첩되어 사용되는 예시 1)

추가적으로 필요에 따라 다음과 같이 해당 함수들을 중첩하여 사용할 수도 있습니다.

 

 

 

 

< 참고 자료 >
https://gent.tistory.com/331