[Oracle SQL] SQL 함수 - 단일행 함수 (4) 변환 함수

오라클의 데이터 타입은 숫자, 문자, 날짜 세 가지입니다. 변환 함수를 통해 데이터 형을 명시적으로 변환시킬 수 있습니다. 

 

함수	설명
TO_NUMBER	문자 데이터를 숫자 데이터로 변환
TO_DATE	문자 데이터를 날짜 데이터로 변환
TO_CHAR	숫자나 날짜 데이터를 문자 데이터로 변환

 

 

1. TO_CHAR 함수

 

숫자나 날짜 데이터를 문자 데이터로 변환합니다. 데이터 형 변환시 어떤 형식으로 출력할지 지정할 수 있습니다. 문자 데이터로 반환되므로 출력 형식은 반드시 작은따옴표로 기술해야 합니다.

TO_CHAR(number|date, 'format')

 

중간에 쉼표가 들어간 형식의 문자 형태로 변환하는 SQL문입니다. 숫자 데이터가 문자 데이터로 변환되었습니다. 

SELECT TO_CHAR(123456,'999,999') FROM dual;  --> 123,456

 

입사 날짜를 네 자리의 연도로 출력합니다. 날짜 데이터가 문자 데이터로 변환되었습니다.

SELECT TO_CHAR(hiredate,'YYYY') FROM emp;  --> 1980, 1981...

 

다음은 날짜 데이터를 문자 데이터로 변환할 때 표현 가능한 방법입니다.

 

날짜 형식	설명	예시
YYYY	연도 네 자리 표현	2022
YY	연도 두 자리 표현	22
MM	월을 숫자로 표현	05
MON	월을 알파벳으로 표현	5월
DD	일을 숫자로 표현	10
DAY	요일 표현	화요일
DY	요일을 약어로 표현	화

 

다음은 시간을 출력하는 형식입니다.

 

시간 형식	설명
AM 또는 PM	오전(AM), 오후(PM) 시각 표현
A.M 또는 P.M	오전(A.M), 오후(P.M) 시각 표현
HH 또는 HH12	시간(1~12) 표현
HH24	시간(0~23) 표현
MI	분 표현
SS	초 표현

 

이를 활용해 다음과 같이 현재 날짜를 출력할 수 있습니다. 

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD,(AM) DY HH24:MI:SS') FROM dual;  
--> 2022/05/10,(오후) 화 22:12:51

 

다음은 입사 날짜가 9월인 사원들의 정보를 출력하는 SQL문입니다. TO_CHAR 함수를 통해 입사 날짜에서 월을 문자 데이터로 바꿔서 출력하고, 그 값이 '09'와 같은 사람들을 검색한 것입니다.

SELECT ename, hiredate FROM emp WHERE TO_CHAR(hiredate,'MM')='09';

 

출력 형식에 한글 표현식을 추가하기 위해서는 다음과 같이 큰따옴표를 사용해야 합니다. 문자열은 작은따옴표로 기술해야 하는데, 이미 작은따옴표가 사용되고 있으므로 작은따옴표 내에서는 큰따옴표로 기술해야 합니다. 

SELECT TO_CHAR(SYSDATE,'YYYY "년" MM "월" DD "일"') "날짜" FROM dual;
--> 2022 년 05 월 10 일

 

숫자를 문자 형식으로 변환할 때, 다음과 같은 숫자 형식을 사용할 수 있습니다. 9를 통해 자리수를 표현하고, 앞부분은 0으로 표현하는 SQL문입니다. 

SELECT TO_CHAR(1111,'099999') FROM dual;  --> 001111

 

숫자 앞에 달러 기호를 붙일 수도 있으며, L을 통해 지역 통화를 붙일 수도 있습니다.

SELECT ename, sal, TO_CHAR(sal,'$999,999'), TO_CHAR(sal,'L999,999') FROM emp;

 

 

 

2. TO_NUMBER 함수

 

숫자 형태의 문자 데이터를 숫자 데이터로 변환합니다. str(string)은 문자열을 의미합니다.

TO_NUMBER(str)

그런데 여기서 문자 데이터는 숫자 데이터로 묵시적으로 변환됩니다. 

 

* 묵시적 / 명시적

묵시적이라는 것은 시스템이 자동으로 작업을 해준다는 것을 의미하며, 명시적이라는 것은 개발자가 수동으로 직접 기재하여 동작하도록 하는 것을 의미합니다. SQL문의 데이터 타입을 변환시키는 방법은 묵시적 방법과 명시적 방법 두 가지가 있는데, 묵시적 방법은 오라클이 자동으로 데이터 타입을 변환시키는 방법이고, 명시적 방법은 변환 함수를 사용하여 데이터를 직접 변환시키는 방법입니다. 

 

문자 데이터를 자동으로 숫자 데이터로 변환하는 묵시적 방법은 다음의 사례에서 볼 수 있습니다. 

오라클에서 식별자가 아닌 문자와 날짜에는 작은따옴표를 붙여야 하지만, 숫자에는 붙이지 않습니다. 그런데 다음과 같이 숫자에 작은따옴표를 붙여도 오류가 나지 않습니다. 

SELECT * FROM emp WHERE deptno='30';
SELECT '123'+100 FROM dual;

숫자를 문자 형태로 입력했는데, 오라클이 이 문자 데이터를 숫자 데이터로 묵시적으로 변환해 준 것입니다. 

 

이렇듯 문자에서 숫자로의 형 변환은 자동으로 되지만, 명시적으로 TO_NUMBER 함수를 사용하는 것이 가독성을 높일 수 있습니다. 다음은 문자 '123'을 숫자 123으로 명시적으로 변환시킨 후 연산하는 SQL문입니다.

SELECT TO_NUMBER('123')+100 FROM dual;

 

 

 

3. TO_DATE 함수

 

날짜 형태의 문자 데이터를 날짜 데이터로 변환합니다. 

TO_DATE(str, 'format')

 

다음과 같이 문자열이 날짜 데이터로 출력됩니다. 

SELECT TO_DATE('20170802','YYYYMMDD') FROM dual;  --> 17/08/02

 

17/08/02 형식으로 출력되는 이유는 오라클이 사용하는 RR/DD/MM 형식 때문입니다. 다음의 SQL문으로 현재 파라미터 설정값을 확인할 수 있습니다.

SELECT * FROM NLS_SESSION_PARAMETERS;

 

이 기본 형식을 다른 형식으로 변경하기 위해 다음과 같이 ALTER 명령문을 사용해 보겠습니다.

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

 

형식이 바뀌어 출력되는 것을 확인할 수 있습니다. 

SELECT TO_DATE('20170802181030','YYYYMMDDHH24MISS') FROM dual;
--> 2017/08/02 18:10:30

 

현재 날짜가 2022년 04월 30일로부터 며칠이 지났는지 알고 싶을 때, TO_DATE 함수를 이용하면 '20220430'이라는 문자열을 날짜 데이터로 변환시켜서 연산을 가능하게 할 수 있습니다. 

SELECT SYSDATE, SYSDATE-TO_DATE('20220430','YYYYMMDD') FROM dual;

 

 

단일행 함수 중 변환 함수에 대해 정리해 보았습니다. 다음 글에서는 조건 함수에 대해 알아보도록 하겠습니다.