오라클(SQL)형변환 함수(CAST,TO_CHAR, TO_NUMBER, TO_DATE)

변환함수

    -자료형의 변환을 담당하는 함수
    -CAST, TO_CHAR, TO_NUMBER, TO_DATE 가 제공 

 

SELECT MEM_ID, 
       MEM_NAME, 
       MEM_MILEAGE,
       CAST(MEM_MILEAGE AS CHAR(10))
  FROM MEMBER;
  
SELECT SUBSTR(CART_NO,1,8),
       CAST(SUBSTR(CART_NO,1,8) AS DATE) AS "날짜",
       CAST(SUBSTR(CART_NO,9) AS NUMBER) AS "LOGIN된 순번",
       CART_PROD,
       CART_QTY
  FROM CART
 WHERE ROWNUM<=10;

2).TO_CHAR(C|D|N[,FMT])
    -날짜, 숫자, 문자 자료를 지정된 형식의 문자열 자료로 변환
     형식지정문자열(FMT)
-----------------------------------------------------
    형식지정문자          의미
-----------------------------------------------------
     AD, BC ,CC          세기          
     YYYY,YYY,YY,Y      년도
     MONTH,MON,MM,RM    월
     W,WW,IW           주차
     Q                      분기
     DD,DDD,J           일
     DAY,DY,D           주의 요일
     AM,PM, A.M., P.M.  오전오후
     HH,HH12,HH24   시간
     MI                   분
     SS,SSSSS           초
     "문자열"           사용자 정의 문자열

SELECT TO_CHAR(SYSDATE, 'AD CC BC PM AM')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'AD YY')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY MONTH')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY MON')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY RM')FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YYYY-DD')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-DDD')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-J')FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'W')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'WW')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'IW')FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'DAY')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DY')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'D')FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'HH')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH12')FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH24')FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SSSSS')FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YYYY"년"MM"월"DD"일')FROM DUAL;

SELECT '1234'||TO_CHAR(56,'00')||'78'FROM DUAL;


SELECT TO_CHAR(SYSDATE,'YYYYMMDD')||TRIM(TO_CHAR(6,'00'))||'78'FROM DUAL;