115. SQL200제 (016~029) 문자함수, 날짜계산함수

016

SELECT UPPER(ename), LOWER(ename), INITCAP(ename) -- 대문자로 출력/소문자로 출력/첫 번째 철자만 대문자로 출력
FROM emp;
-- upper와 lower함수는 테이블 내 특정 문자 데이터를 검색할 때, 대/소문자 확실하지 않을 때 사용

SELECT ename, sal
FROM emp
WHERE LOWER(ename) = 'scott'; -- SCOTT이 이름이지만 결과 반환

017

SELECT SUBSTR('SMITH', 1, 3) -- 특정 위치의 문자열 추출 (문자열/철자 시작 위치/위치로부터 몇개 추출할지)
FROM dual; -- 결과 SMI

018

SELECT ename, LENGTH(ename)
FROM emp; -- 이름과 이름의 철자 개수 출력

SELECT LENGTH('가나다라마') -- 한글도 가능
FROM dual; -- 결과값 5

SELECT LENGTHB('가나다라마') -- 한글도 가능
FROM dual; -- 결과값 15 (바이트의 길이 반환. 한글은 1자에 3바이트)

019

SELECT INSTR('SMITH', 'M') -- 특정 철자의 위치 출력
FROM dual; -- 결과값 2

SELECT INSTR('abcdefg@naver.com', '@') 
FROM dual; -- 결과값 8

SELECT SUBSTR('abcdefg@naver.com', INSTR('abcdefg@naver.com', '@')+1) -- substr로 @의 자리 숫자 다음 철자를 시작 숫자로 사용해 추출
FROM dual; -- 결과값 naver.com

SELECT RTRIM (SUBSTR('abcdefg@naver.com', INSTR('abcdefg@naver.com', '@')+1), '.com') -- naver만 추출
FROM dual; -- 결과값 naver.com

 

 

020

SELECT ename, REPLACE(sal, 0, '*')
FROM emp; -- 0을 *로 대체

SELECT ename, REGEXP_REPLACE(sal, '[0-3]', '*') as SALARY -- 정규식 함수
FROM emp; -- 월급의 숫자 0~3을 *로 출력

CREATE TABLE TEST_ENAME
(ENAME VARCHAR2(10));

INSERT INTO TEST_ENAME VALUES('김인호');
INSERT INTO TEST_ENAME VALUES('안상수');
INSERT INTO TEST_ENAME VALUES('최영희');

SELECT REPLACE(ename, SUBSTR (ename, 2, 1), '*') as "전광판_이름"
FROM test_ename; -- 김*호, 안*수, 최*희

021

SELECT ename, LPAD(sal, 10, '*') as salary1, RPAD(sal, 10, '*') as salary2 -- 왼쪽으로 채워넣다/오른쪽으로 채워넣다
FROM emp;

SELECT ename, sal, lpad('■', round(sal/100) ,'■') as bar_chart
FROM emp;

 

 

022

SELECT 'smith', LTRIM('smith', 's'), RTRIM('smith', 'h'), TRIM('s' from 'smiths') -- 왼쪽 철자 잘라서/오른쪽 철자 잘라서/양쪽 철자 잘라서 출력
FROM dual; -- smith, mith, smit, mith

insert into emp(empno,ename,sal,job,deptno) values(8291, 'JACK  ', 3000, 'SALESMAN', 30); -- jack이름 뒤 공백 존재
commit;

SELECT ename, sal
FROM emp
WHERE ename='JACK'; -- 출력 결과 없음

SELECT ename, sal
FROM emp
WHERE RTRIM(ename)='JACK'; -- 오른쪽 공백 제거하여 출력됨

DELETE FROM EMP WHERE TRIM(ENAME)='JACK';
COMMIT;

023

SELECT '876.567' as 숫자, ROUND(876.567,1) -- 소수점을 0삼아, 두 번째 자리에서 반올림
FROM dual; -- 876.6

SELECT '876.567' as 숫자, ROUND(876.567,-1) -- 소수점을 0삼아, 소수점 이전 일의 자리에서 반올림
FROM dual; -- 880

024

SELECT '876.567', TRUNC(876.567,1) -- 소수점을 0삼아, 두 번째 자리부터 버리고 출력
FROM dual; -- 876.5

SELECT '876.567', TRUNC(876.567,-1) -- 소수점을 0삼아, 소수점 이전 일의 자리에서 버리고 출력
FROM dual; -- 870

025

SELECT MOD(10,3) -- 10을 3으로 나눈 나머지 값
FROM DUAL; -- 1 

SELECT empno, MOD(empno,2)
FROM emp; -- 사원번호가 홀수면1, 짝수면 0 출력

SELECT empno, ename 
FROM emp
WHERE MOD(empno,2) = 0; -- 사원번호가 짝수인 사람들 출력

SELECT FLOOR(10/3) -- 10을 3으로 나눈 값은 3.333.. FLOOR는 3과 4사이의 가장 바닥값 출력
FROM dual; --3

026

SELECT ename, MONTHS_BETWEEN(sysdate,hiredate) -- 규칙! MONTHS_BETWEEN(최신날짜, 예전날짜) sysdate는 오늘 날짜를 확인하는 함수
FROM emp; 

SELECT TO_DATE('2019-06-01','RRRR-MM-DD') - TO_DATE('2018-10-01','RRRR-MM-DD') -- TO_DATE는 연도/달/일 명시
FROM dual; --243

SELECT ROUND((TO_DATE('2019-06-01','RRRR-MM-DD') - TO_DATE('2018-10-01','RRRR-MM-DD')) / 7 )  AS "총 주수"
FROM dual; -- 35
 
SELECT MONTHS_BETWEEN('2018-10-01', '2019-06-01') as "개월 수" -- 개월 수 계산. month는 30 혹은 31로 나누어야하므로 MONTHS_BETWEEN 사용
FROM dual; -- 8

027

SELECT ADD_MONTHS(TO_DATE('2019-05-01','RRRR-MM-DD') , 100) -- 2019년 5월 1일로부터 100달 뒤의 날짜
FROM dual;  -- 27/09/01

SELECT TO_DATE('2019-05-01','RRRR-MM-DD') + 100 -- 2019년 5월 1일부터 100일 후의 날짜
FROM dual; -- 19/08/09

SELECT TO_DATE('2019-05-01','RRRR-MM-DD') + interval '100' month -- 19년 5월 1일부터 100달 뒤의 날짜
FROM dual; -- 27/09/01

SELECT TO_DATE('2019-05-01','RRRR-MM-DD') + interval '1-3' year(1) to month -- 19년 5월 1일부터 1년 3개월 후의 날짜 출력
FROM dual; -- 20/08/01
  
SELECT TO_DATE('2019-05-01','RRRR-MM-DD') + interval '3' year -- 19년 5월 1일부터 3년 후의 날짜 반환
FROM dual; --22/05/01

SELECT TO_DATE('2019-05-01','RRRR-MM-DD') + TO_YMINTERVAL('03-05') as 날짜 -- 19년 5월 1일부터 3년 5개월 후의 날짜 출력
FROM dual; -- 22/10/01

 

INTERVAL '1' YEAR	1년 더하기
INTERVAL '1' MONTH	1개월 더하기
INTERVAL '1-1' YEAR TO MONTH	1년 1개월 더하기
INTERVAL '1' DAY	1일 더하기
INTERVAL '1' HOUR	1시간 더하기
INTERVAL '03:30' HOUR TO MINUTE	3시간30분 더하기
INTERVAL '1' MINUTE	1분 더하기
INTERVAL '1' SECOND	1초 더하기
INTERVAL '10:30' MINUTE TO SECOND	10분30초 더하기

* 시간과 분 / 분과 초는 : 구분자, 연도와 개월은 - 구분자

 

 

028

SELECT '2019/05/22' as 날짜, NEXT_DAY('2019/05/22', '월요일') -- 19년 5월 22일부터 바로 돌아올 월요일의 날짜
FROM dual; -- 19/05/27
  
SELECT SYSDATE as 오늘날짜 -- 오늘 날짜 출력
FROM dual; 

SELECT NEXT_DAY(SYSDATE, '화요일') as "다음 날짜" -- 오늘부터 앞으로 돌아올 화요일 날짜 출력
FROM dual; 

SELECT NEXT_DAY(ADD_MONTHS('2019/05/22',100), '화요일') as  "다음 날짜" -- 오늘부터 100달 뒤에 돌아오는 화요일 날짜 출력
FROM dual; 
  
SELECT NEXT_DAY(ADD_MONTHS(sysdate,100), '월요일') as "다음 날짜" -- 오늘부터 100달 뒤에 돌아오는 월요일 날짜 출력
FROM dual;

  
029

SELECT '2019/05/22' as 날짜, LAST_DAY('2019/05/22') as "마지막 날짜" -- 19년 5월 22일 해당 달의 마지막 날짜
FROM dual; -- 19/05/31
  
SELECT LAST_DAY(SYSDATE) - SYSDATE as 남은날짜 -- 오늘부터 이번 달 말일까지 남은 일수
FROM dual;
  
SELECT ename, hiredate, LAST_DAY(hiredate) -- 입사한 달의 마지막 날짜 출력
FROM emp
WHERE ename = 'KING'; -- 81/11/30

 

 

 

 

참고

interval 함수 이해 https://cross-the-line.tistory.com/4