[SQL] 오라클(Oracle) 형변환 함수 정리

TO_NUMBER(문자열)

입력받은 문자열을 숫자로 변환하여 반환한다. 만약, 문자열에 수치 이외의 문자가 포함되어 있으면 오류가 발생한다.

SELECT TO_NUMBER('12345') AS CASE1,
    TO_NUMBER('12.345') AS CASE2
FROM dual;

CASE1	CASE2
12345	12.345

 

만약, 여러 문자가 섞인 문자열을 숫자로 변환하고자 한다면, REPLACE와 정규식을 활용하여 변환할 수 있다.

SELECT TO_NUMBER(REPLACE(REPLACE('$123,456.78', '$'), ',') AS CASE1,
    TO_NUMBER(REGEXP_REPLACE('$123,456.78', '[^0-9.]', '') AS CASE2
FROM dual;

CASE1	CASE2
123456.78	123456.78

 

 

TO_DATE(문자열, 포맷) / TO_TIMESTAMP(문자열, 포맷)

문자열을 입력받아 포맷에 알맞은 날짜 데이터(DATE)로 변환하여 반환한다.

TO_DATE 함수를 활용하기 위해서는 날짜 데이터의 포맷을 알아야 한다.

표기	설명
YYYY	4자리 년도
YY	2자리 년도
MM	숫자 형태의 월
DD	31일 형태의 일
DDD	365일 형태의 일
DAY	영문 요일
HH24	24시 형태의 시
HH12	12시 형태의 시
MI	초
SS	초

SELECT TO_DATE('20231231235959', 'YYYYMMDDHH24MISS') AS CASE1,
    TO_DATE('202312312359', 'YYYYMMDDHH24MISS') AS CASE2,
    TO_DATE('2023123123', 'YYYYMMDDHH24MISS') AS CASE3,
    TO_DATE('20231231', 'YYYYMMDDHH24MISS') AS CASE4,
    TO_DATE('202312', 'YYYYMM') AS CASE5,
    TO_DATE('2023', 'YYYY') AS CASE6
FROM dual;

CASE1	CASE2	CASE3	CASE4	CASE5	CASE6
2023-12-31 23:59:59	2023-12-31 23:59:00	2023-12-31 23:00:00	2023-12-31 00:00:00	2023-12-01 00:00:00	2023-01-01 00:00:00

 

 

TO_CHAR(날짜 또는 숫자, 포맷)

날짜 데이터(DATE) 또는 숫자 데이터(NUMBER)를 입력받아 문자열로 변환하여 반환한다.

오라클에서 제공하는 포맷을 통해 숫자나 날짜 데이터를 다양한 형태의 문자열로 반환할 수 있다.

• 포맷을 지정하지 않고 생략할 수도 있다.

 

문자열로 변환

SELECT TO_CHAR(123456) AS CASE1,
    TO_CHAR(12.34) AS CASE2,
    TO_CHAR(SYSDATE) AS CASE3
FROM dual;

CASE1	CASE2	CASE3
123456	12.34	2023-04-27 11:20:35

 

날짜 데이터를 문자열로 변환

SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS CASE1,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CASE2,
    TO_CHAR(SYSDATE, '""YYYY"년 "MM"월 "DD"일 "HH24"시 "MI"분 "SS"초"') AS CASE3
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') AS CASE4,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24') AS CASE5,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS CASE6,
    TO_CHAR(SYSDATE, 'YYYY-MM') AS CASE7,
    TO_CHAR(SYSDATE, 'YYYY') AS CASE8,
    TO_CHAR(SYSDATE, 'YY') AS CASE9
FROM dual;

CASE1	CASE2	CASE3	CASE4	CASE5	CASE6	CASE7	CASE8	CASE9
20230427112035	2023-04-27 11:20:35	2023-04-27 11:20	2023년 04월 27일 11시 20분 35초	2023-04-27 11	2023-04-27	2023-04	2023	23

 

숫자 데이터를 문자열로 변환

SELECT TO_CHAR(123456, '$999,999.00') AS CASE1,
    TO_CHAR(12.45678, 'FM90.99') AS CASE2,
    TO_CHAR(12345678, 'FML99,999,999') AS CASE3,
    TO_CHAR(123, 'FM000000') AS CASE4
FROM dual;

CASE1	CASE2	CASE3	CASE4
$123,456.00	12.46	₩12,345,678	000123

• FM : 좌우 공백 제거
• 9 : 값이 없으면 제거
• 0 : 값이 없으면 0으로 채움