[Oracle | 오라클] 단일행 함수 - 4. 변환함수

※ 이 글은 오지영 강사님의 <업무에 바로 쓰는 SQL 활용실습>의 강의내용을 바탕으로 작성하였습니다.

 

※출처 업무에 바로 쓰는 SQL 활용실습 

 

 

 

1. TO_CHAR 문자열로 변환하는 함수

현재날짜가 어느 요일인지 조회하기(월화수목금토일 중 하나)

SELECT SYSDATE, to_char(SYSDATE, 'day')

FROM dual;

 

현재날짜가 1년 365일 중에 몇 번째 날인지 조회하기

SELECT SYSDATE, to_char(SYSDATE, 'ddd')

FROM dual;

 

현재날짜가 해당 월 기준으로 몇 번째 날인지 조회하기

SELECT SYSDATE, to_char(SYSDATE, 'dd')

FROM dual;

 

현재날짜가 해당 주 기준으로 몇 번째 날인지 조회하기

SELECT SYSDATE, to_char(SYSDATE, 'd')

FROM dual;

현재날짜를 OOOO년도 OO월로 조회하기 

SELECT SYSDATE, to_char(SYSDATE, 'yyyymm')

FROM dual;

현재날짜가 자정을 기준으로 몇 초 지났는지 조회하기

SELECT SYSDATE, to_char(SYSDATE, 'sssss')

FROM dual;

 

숫자를 'OOO,OOO'의 형태로 조회하기(지정한 자릿수보다 변환하려는 수의 자릿수가 부족하다면 남은 자릿수 자동으로 생략)

SELECT 12345, TO_CHAR(12345, '999,999')

FROM dual;

SELECT 12345, TO_CHAR(-12345, '999,999')

FROM dual;

SELECT 1234567, TO_CHAR(1234567, '999,999,99')

FROM dual;

숫자를 'OOO,OOO'의 형태로 조회하기 (지정한 자릿수보다 변환하려는 수의 자릿수가 부족하면 남은 자릿수를 0으로 채움)

SELECT 12345, TO_CHAR(12345, '000,000')

FROM dual;

 

SELECT 12345, TO_CHAR(12345, '111,111')

FROM dual; --에러

 

마이너스 부호(-)를 숫자 뒤에 붙여서 조회하기 

SELECT 12345, TO_CHAR(-12345, '999,999mi')

FROM dual;

숫자에 괄호(< >를 포함하여 조회하기 

SELECT 12345, TO_CHAR(-12345, '999,999pr')

FROM dual;

 

 

SELECT 12345, TO_CHAR(12345, '111,111')

FROM dual; --에러

 

변환하려는 수가 지정한 자릿수보다 많을 때 숫자가 지정되지 않은 자리는 #으로 표시 됨.

SELECT 12345, TO_CHAR(1234567, '999,999')

FROM dual;

 

 

2. TO_DATE  날짜형으로 변환하는 함수

to_date함수를 이용해서 날짜로 생성하면 년도/월/일 시분초 정보가 자동으로 세팅된다(사용자가 지정하지 않은 값들은 기본값으로 저장된다).

SELECT TO_DATE('1212','yymm') 결과1,  TO_DATE( '1212','mmdd') 결과2

FROM dual; 

-- 결과1에서 일(DAY)는 1일로, 결과2에서 년도(YEAR)는 sysdate(현재날짜)의 년도로 설정된다. 

※ to_date 함수 사용시 주의점

존재하지 않는 날(DAY)이나 월(MONTH)로 세팅할 시 에러가 난다.

SELEST TO_DATE( '1225','mmdd') 

FROM dual;

SELECT TO_DATE( '1225','yymm') 

FROM dual;  --에러

SELECT TO_DATE( '0230','mmdd') 

FROM dual;  --에러

년도 지정시 끝 2자리만 사용한 경우

SELECT TO_DATE('77','yy') 결과1, TO_DATE('33','yy') 결과2

FROM dual ;

--yy옵션은 sysdate 년도 앞2자리를 사용함.

SELECT TO_DATE('77','rr') 결과1, TO_DATE('33','rr') 결과2

FROM dual ;

--rr옵션은 00-49까지는 20을 50-99까지는 19를 앞자리로 사용.

 

---

 

연습문제

 1. player_t 선수 중 K10팀 선수들의 이름, 생일, 태어난 요일 조회하기

  SELECT player_name, birth, TO_CHAR(birth,'day')

  FROM   player_t

  WHERE team_id ='K10' ;

  *몇 가지 함수 더 이용해보기

  SELECT player_name, birth, 

  TO_CHAR(birth,'day') ,

  LENGTH(TO_CHAR(birth,'day')),

  RPAD(  TO_CHAR(birth,'day'),10,'*') 

  FROM   player_t

  WHERE team_id ='K10' ;

 

2. 일요일에 태어난 선수들만 이름 조회하기

  SELECT  player_name

  FROM    player_t

  WHERE trim(TO_CHAR( birth,'day')) = 'sunday' ;

 

  SELECT  player_name

  FROM    player_t

  WHERE to_char( birth,'dy') = 'sun' ;

  --한글셋팅으로 되어있는경우 sun, sunday 대신 일 또는 일요일로 지정해야함.

 SELECT  player_name

 FROM    player_t

 WHERE to_char( birth,'d') = '1';

 

3.player_t 선수들의 이름, 생일, 60번째 생일이 무슨 요일인지 조회하기

 

 각 선수들 60번째 생일 : ADD_MONTHS(birth,12*60)

 

 60번째 생일의 요일 확인 : TO_CHAR(60번째생일,'day') 

  

 SELECT  player_name, birth, TO_CHAR(ADD_MONTHS(birth,12*60),'day') 

 FROM  player_t ;

 

4.player_t 선수중에서 1973년12월 25일에 태어난 선수들 이름, 팀id 조회하기

날짜타입을 문자타입과 비교

(문자를 날짜로 DB내부에서 변환시도. 변환작업 성공시 결과 나오기도 함) 

SELECT player_name, team_id 

FROM player_t

WHERE birth = '1973-12-25' ;

          날짜       문자 

               

문자타입으로 변환해서 비교

SELECT player_name, team_id 

FROM player_t

WHERE TO_CHAR(birth,'yyyymmdd') = '19731225' ;

                    문자                        문자

날짜타입으로 변환해서 비교

(최대한 기존 컬럼에 변형을 가하지 않는 것이 성능 향상에 도움이 된다. 이 방법이 가장 효과적이다.)SELECT player_name, team_id 

FROM player_t

WHERE birth  = TO_DATE('19731225','yyyymmdd') ;

         날짜                      날짜 

그 외 방법

SELECT player_name, team_id 

FROM player_t

WHERE  birth  >= TO_DATE('19731225','yyyymmdd') 

   AND   birth <   TO_DATE('19731226','yyyymmdd') ; 

SELECT player_name, team_id 

FROM player_t

WHERE birth BETWEEN TO_DATE('19731225','yyyymmdd') 

    AND TO_DATE('19731225:23:59:59','yyyymmdd:hh24:mi:ss') ;

5.1975년도에 태어난 선수이름, 생일 조회하기

SELECT player_name, birth

FROM player_T

WHERE TO_CHAR( birth,'yyyy') = '1975' ;

SELECT player_name, birth 

FROM player_t

WHERE   birth >= TO_DATE('1975','yyyy') 

   AND   birth <   TO_DATE('1976','yyyy') ;

SELECT player_name, birth 

FROM player_t

WHERE  birth >= TO_DATE('197501','yyyymm') 

   AND  birth  < TO_DATE('197601','yyyymm') ;

 

 

6. 2099년 12월 25일 무슨 요일인지 조회하기 | TO_CHAR( 날짜, 'day') 

SELECT TO_CHAR( '2099-12-25','day') 

FROM dual ;

           

SELECT TO_CHAR( TO_DATE('20991225','yyyymmdd'),'DAY')

FROM dual ;

 

 

7. 각 선수들의 올해 생일이 무슨 요일인지 조회하기

     

올해년도 생일 만들기

 

1) 년도는 2015년도/ 월일은 birth의 월일 정보를 이용

'2015' || TO_CHAR( birth,'mmdd')

 

2) 이를 다시 날짜형식으로 변환

TO_DATE('2015' || TO_CHAR( birth,'mmdd'),'yyyymmdd')

 

3) 최종적으로 만들어진 날짜를 이용하여 요일은 조회
TO_CHAR( 2번날짜, 'day') 

SELECT player_name, birth, TO_CHAR( TO_DATE('2015' || TO_CHAR( birth,'mmdd'),'yyyymmdd') ,'DAY') 

FROM player_t

WHERE birth is not null ;