[1일1코딩] [SQL][ORACLE] SUBSTR, REPLACE, 주민번호 뒷자리 별처리, FLOOR/TRUNC, MOD, SYSDATE, ADD_MONTHS, NEXT_DAY, LAST_DAY, EXTRACT

SUBSTR, REPLACE

FLOOR/TRUNC, 

MOD, 

 

SYSDATE, 

MONTHS_BETWEEN, ADD_MONTHS,

NEXT_DAY, LAST_DAY, EXTRACT

--SUBSTR
-- String.substring()
--SUBSTR(컬럼명, 시작위치 인덱스, 시작위치부터 값을 반환할 길이)


-- HELLOMYGOODFRIENDS // YGOODFRIENDS
-- 7번째서부터 끝까지 반환
SELECT SUBSTR('HELLOMYGOODFRIENDS',7) FROM DUAL;

-- HELLOMYGOODFRINEDS // OM
-- 5번째 문자부터 2개만 반환
SELECT SUBSTR('HELLOMYGOODFRIENDS',5,2) FROM DUAL;

-- HELLOMYGOODFRINEDS // (null)
-- 5번쨰 문자부터 0개 반환 // 0개 반환이 불가능하기에 NULL 출력됨
SELECT SUBSTR('HELLOMYGOODFRIENDS',5,0) FROM DUAL; -- --(NULL)
-- HELLOMYGOODFRINEDS // HELLOM
SELECT SUBSTR('HELLOMYGOODFRIENDS',0,5) FROM DUAL; -- HELLO // 0부터 시작해도 1부터 적용
SELECT SUBSTR('HELLOMYGOODFRIENDS',1,5) FROM DUAL; -- HELLO // 0부터 시작해도 1부터 적용


-- HELLOMYGOODFRINEDS //  DFR
-- 뒤에서 8번째(D)부터 오른쪽으로 3개의 문자 반환
-- 위에서 마이너스(-)였으면 기준점이 뒤에서부터 세고 왼쪽방향으로 카운팅했지만 얘는 오른쪽으로 카운팅
SELECT SUBSTR('HELLOMYGOODFRIENDS',-8,3) FROM DUAL; -- DFR
-- HELLOMYGOODFRINEDS // OO
SELECT SUBSTR('HELLOMYGOODFRIENDS',-10,2) FROM DUAL;  -- OO
SELECT SUBSTR('HELLOMYGOODFRIENDS',-10,-2) FROM DUAL; -- 3RD 인자 몇번째문자인지가 -이면 NULL값


-- EMPLOYEE 테이블에서 이름, 이메일, 이메일의 아이디 조회
-- (이메일의 @ 도메인말고 아이디까지만)
SELECT EMP_NAME, EMAIL, SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1)
FROM EMPLOYEE;
-- 문제풀이 사고과정
-- 1.이메일 아이디의 규칙은 항상 @이 앞에 있음
-- 2. @ 위치 파악
-- 3. 1~@앞까지 찾기

-- EMPLOYEE 테이블에서 이름, 이메일, 이메일의 아이디 조회
-- (이메일의 @ 도메인말고 아이디까지만)
SELECT EMP_NAME, EMAIL,SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1)
FROM EMPLOYEE;


-- 주민등록번호를 이용하여 이름과 성별을 나타내는 부분 조회
-- EMPLOYEE 테이블에서 남자만 조회(사원명, '남')
-- EMPLOYEE 테이블에서 여자만 조회(사원명, '여')
SELECT EMP_NAME 사원명, CASE WHEN SUBSTR(EMP_NO,8,1)=1 THEN '남'
                            WHEN SUBSTR(EMP_NO,8,1)=2 THEN '여'
                       END 성별 
FROM EMPLOYEE;

SELECT EMP_NAME 사원명, DECODE(SUBSTR(EMP_NO,8,1),1,'남',2,'여') 성별
FROM EMPLOYEE;



-- EMPLOYEE테이블에서 직원들의 주민번호를 이용하여 사원명, 생년, 생월, 생일 조회
SELECT EMP_NAME 사원명,  SUBSTR(EMP_NO,1,2) 생년, SUBSTR(EMP_NO,3,2) 생월, SUBSTR(EMP_NO,5,2) 생일
FROM EMPLOYEE;



------------------------------------------------------------------------------
------------------------------------------------------------------------------


-- LOWER / UPPER / INITCAP
-- INITCAP :각 단어의 첫글자만 대문자

-- 예제 Welcome To my World
-- welcome to my world
SELECT LOWER('Welcome To my World') FROM DUAL;
-- WELCOME TO MY WORLD
SELECT UPPER('Welcome To my World') FROM DUAL;
-- Welcome To My World
SELECT INITCAP('Welcome To my World') FROM DUAL;


------------------------------------------------------------------------------
------------------------------------------------------------------------------

-- CONCAT
-- 문자열 이어붙이기

-- 가나다라123
SELECT CONCAT('가나다라','123') FROM DUAL;
SELECT CONCAT(DEPT_CODE, '-333') FROM EMPLOYEE; -- D9-333
SELECT CONCAT(JOB_CODE, ' BY S') FROM JOB; -- J1 BY S
SELECT CONCAT(DEPT_CODE, JOB_CODE) FROM EMPLOYEE; -- D9J1
-- 연결연산자 사용 : 가나다라123
-- 연결연산자 : ||
SELECT '가나다라' || '123' FROM DUAL; -- 가나다라123


------------------------------------------------------------------------------
------------------------------------------------------------------------------

-- REPLACE
-- REPLACE(컬럼명,바꿔질 단어, 바꿀 단어)
-- 값 교체

-- 서울시 강남구 역삼동 // 서울시 강남구 삼성동
SELECT REPLACE('서울시 강남구 역삼동','역삼동','삼성동') FROM DUAL;

-- 강사님은 오라클을 수업중이다 // 선생님은 오라클을 수업중이다
-- 띄어쓰기 상관없이 적용가능
SELECT REPLACE('강사님은 오라클을 수업중이다','강사님','선생님') FROM DUAL;

-- 케이크 달다. 케이크 싫은사람? // 공부 달다. 공부 싫은사람?
-- 여러개 들어있어도 복수로 교체가능. 일치만 하면 전부 교체함
SELECT REPLACE('케이크 달다. 케이크 싫은사람?','케이크','단 거') FROM DUAL;

-- EMPLOYEE테이블에서 사원명, 주민번호 조회
-- 주민번호의 뒷자리는 *로 바꿔서 조회(EX.001122-*******)


-- REPLACE+SUBSTR

-- REPLACE+SUBSTR+INSTR


/*
REPLACE(EMP_NO,SUBSTR(EMP_NO,8,14/2)
SELECT REPLACE(EMP_NO,SUBSTR(EMP_NO,8,14/2)) FROM EMPLOYEE;
SELECT REPLACE(EMP_NO,SUBSTR(EMP_NO,8,LENGTH(EMP_NO)/2)) FROM EMPLOYEE;
SELECT LENGTH(EMP_NO)/2 FROM EMPLOYEE;
*/

-- RPAD+SUBSTR
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO,1,7),14,'*******')
FROM EMPLOYEE;


-- 방법1
-- REPLACE+SUBSTR 끝수 지정 & 미지정 


-- 방법2
-- RPAD+SUBSTR
SELECT EMP_NAME, REPLACE(EMP_NO,SUBSTR(EMP_NO,8,7),'*******') 주민번호
FROM EMPLOYEE;

-- RPAD+SUBSTR+INSTR
SELECT EMP_NAME, REPLACE(EMP_NO,SUBSTR(EMP_NO,8,7),'*******')
FROM EMPLOYEE;

-- RPAD+SUBSTR+INSTR+LENGTH
SELECT EMP_NAME, REPLACE(EMP_NO,SUBSTR(EMP_NO,INSTR(EMP_NO,'-')+1,LENGTH(EMP_NO)/2),'*******')
FROM EMPLOYEE;

-- 방법3
-- REPLACE+SUBSTR+INSTR+LENGTH


-- 방법4
-- SUBSTR+연결연산자(||)
SELECT EMP_NAME, SUBSTR(EMP_NO, 1, 7) || '*******' 주민번호
FROM EMPLOYEE;



------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------

-- 2) 숫자 관련 함수


-- ABS
-- 절대값 반환
SELECT ABS(FLOOR(1.234)) FROM DUAL;
-- 10.9	10.9	10	10
-- 전부 양수 출력
SELECT ABS(-10.9), ABS(10.9), ABS(-10) FROM DUAL;

-- MOD
-- 모듈러
-- 나머지 구하기
-- MOD(나눠지는수 ,나누는 수)

-- 1	-1	1	-1	1.9
-- 내가 나누어지는 수에 대한 부호를 따라가게 됨
SELECT MOD(10,3), MOD(-10,3), MOD(10,-3), MOD(-10,-3), MOD(10.9,3) FROM DUAL;


-- ROUND
-- 몇번째 자리에서 반올림할지 소수점 자리수 지정가능
-- 123.456
-- 123	124	123	123.5	123.46	120	100
-- 소수점 자리지정 반올림
-- 소수점 -의 반올림
SELECT ROUND(123.456,0),ROUND(123.456), ROUND(123.456,1),ROUND(123.456,2), ROUND(123.456,-1) FROM DUAL;
SELECT ROUND(123.456,-1), ROUND(123.456,-2) FROM DUAL;

-- -11
-- 마이너스(-)의 반올림은?
-- 마이너스는 숫자가 클수록 작은 수 ex) -11이 -10보다 작은 수
-- -10.61에서 .61이 5이상이라 반올림이 되는데 -11로



-- CEIL
-- 124
-- 무조건 1의 자리에서 올림하게 되어있음
-- 자릿수 지정 불가. 다른 인자값 집어넣으면 에러
SELECT CEIL(123.456) FROM DUAL;


-- FLOOR / TRUNC
-- FLOOR : 수학적 내림(배웠던 수학의 내림) // 자릿수 지정 불가
-- TRUNC : 버림. 절삭. 몇번째 자리까지 버리겠다 // 자릿수 지정가능
SELECT FLOOR(123.456), TRUNC(123.456) FROM DUAL;
SELECT FLOOR(123.456), TRUNC(123.456,1) FROM DUAL;

-- 123	123	123.7
-- TRUNC : 몇번째 자리까지 버리겠다 자릿수 지정가능
-- 파이썬에서 FLOOR가 -쪽으로 내리고 TRUNC가 0쪽으로 수렴하는 것과 같은 개념
-- math.trunc(-3.14)   #결과는 -3
-- math.floor(-3.14)   #결과는 -4






------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------



-- 3)날짜 관련 함수

-- SYSDATE
-- 시스템에 저장되어있는 시간을 사용

SELECT SYSDATE FROM DUAL; -- 22/03/14



-- MONTHS_BETWEEN
-- ex) MONTHS_BETWEEN(SYSDATE, HIRE_DATE)
-- 개월수의 차를 숫자로 리턴해주는 함수
SELECT MONTHS_BETWEEN(SYSDATE, HIRE_DATE) FROM EMPLOYEE; -- 385일 차이
SELECT SYSDATE,HIRE_DATE, MONTHS_BETWEEN(SYSDATE, HIRE_DATE) FROM EMPLOYEE;
SELECT SYSDATE,HIRE_DATE, ABS(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) FROM EMPLOYEE;


-- EMPLOYEE테이블에서 사원의 이름, 입사일, 근무 개월 수 조회
SELECT EMP_NAME, HIRE_DATE, MONTHS_BETWEEN(SYSDATE,HIRE_DATE) "근무 개월 수"
FROM EMPLOYEE;

SELECT EMP_NAME, HIRE_DATE, CEIL(ABS(MONTHS_BETWEEN(HIRE_DATE, SYSDATE))) || '개월차'
FROM EMPLOYEE; -- 368개월차
-- 앞뒤로 뭐가 올지 모른다면 ABS를 넣어서 절대값으로 받아오면 된다

SELECT EMP_NAME, HIRE_DATE, CEIL(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) || '일수'"근무일수" FROM EMPLOYEE;




-- ADD_MONTHS
-- 기준 날짜에다 지정한 숫자만큼의 개월수로 더한 날짜 리턴
SELECT ADD_MONTHS(SYSDATE,4) FROM EMPLOYEE; -- 22/07/19
-- 지금부터 4개월 뒤 반환
SELECT ADD_MONTHS(SYSDATE,12) FROM EMPLOYEE; -- 23/03/19
-- 개월수가 더해져서 연도가 넘어가면 연도도 올라감

--EMPLOYEE테이블에서 사원의 이름, 입사일, 입사 후 6개월이 된 날짜 조회
SELECT EMP_NAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE,6) FROM EMPLOYEE;
SELECT EMP_NAME, ADD_MONTHS('00/01/01',12) FROM EMPLOYEE; -- 01/01/01
-- 마이너스 -12개월
SELECT EMP_NAME, ADD_MONTHS('00/01/01',-12) FROM EMPLOYEE; -- 99/01/01



-- NEXT_DAY
-- 기준 날짜에서 구하려는 요일에 가장 가까운 날짜 리턴
-- 1=일, 2=월, 3=화,4=수, 5=목, 6=금, 7=토
-- 텍스트의 맨 앞글자만 따와서 요일 반환

SELECT SYSDATE, NEXT_DAY(SYSDATE, '일') FROM DUAL; -- 22/03/19	22/03/20
SELECT SYSDATE, NEXT_DAY(SYSDATE, '토') FROM DUAL; -- 22/03/19	22/03/26

-- 지금 기준으로부터 가장 가까운 목요일 구하기
SELECT NEXT_DAY(SYSDATE, '목') FROM DUAL;    -- 22/03/24
SELECT NEXT_DAY(SYSDATE, '목요일') FROM DUAL; -- 22/03/24
SELECT NEXT_DAY(SYSDATE, 5) FROM DUAL;       -- 22/03/24   
-- 1=일, 2=월, 3=화,4=수, 5=목, 6=금, 7=토
SELECT NEXT_DAY(SYSDATE, 'THUR') FROM DUAL; -- ERROR :  not a valid day of the week



SELECT SYSDATE, NEXT_DAY(SYSDATE, '목성주기') FROM DUAL; -- 22/03/19	22/03/24
-- 요일과 관련 없는 단어지만 맨 앞글자 '목'만 따서 요일 반환하는 걸 알 수 있다
SELECT SYSDATE, NEXT_DAY(SYSDATE, '금성') FROM DUAL; -- 22/03/19	22/03/25
-- 금성의 맨 앞글자 '금' = 금요일


-- 한글만 인지하게끔 설정이 되어있어 인식오류 뜨는 것

-- 영어로 세팅
ALTER SESSION SET NLS_LANGUAGE = AMERICAN; -- Session이(가) 변경되었습니다.
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSDAY') FROM DUAL; -- 22/03/15	22/03/17
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THUR') FROM DUAL; -- 22/03/15	22/03/17
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSTY') FROM DUAL; -- 22/03/15	22/03/17
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THUR--') FROM DUAL; -- 22/03/15	22/03/17
-- 한글과 동일하게 앞글자만 맞으면 요일 반환. 아마도 첫발음의 한글자를 치는 듯

SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURDAY') FROM DUAL; -- 22/03/19	22/03/24
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THUR') FROM DUAL;    -- 22/03/19	22/03/24
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THUR---') FROM DUAL;


-- 한글로 세팅
ALTER SESSION SET NLS_LANGUAGE = KOREAN; -- Session이(가) 변경되었습니다.

SELECT NEXT_DAY(SYSDATE, '금') FROM DUAL; -- 22/03/25
SELECT NEXT_DAY(SYSDATE, '금성') FROM DUAL;


-- LAST_DAY
-- 해당 날짜의 월의 마지막 일 반환
SELECT LAST_DAY(SYSDATE) FROM DUAL; -- 22/03/31
SELECT LAST_DAY(HIRE_DATE) FROM EMPLOYEE; -- 90/02/28 01/09/30
SELECT LAST_DAY(JOB_CODE) FROM EMPLOYEE; -- ERROR
-- ORA-01841: (full) year must be between -4713 and +9999, and not be 0


-- EXTRACT
-- 년, 월, 일 정보 추출 반환
-- 시간은 추출 불가

SELECT EXTRACT(SYSDATE-hire_date) FROM EMPLOYEE;

SELECT EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE),
       EXTRACT(DAY FROM SYSDATE) FROM EMPLOYEE; -- 2022	3	19
SELECT EXTRACT(DAY FROM SYSDATE)
--        EXTRACT(HOUR FROM SYSDATE),  -- 시간은 추출 불가
--        EXTRACT(MINUTE FROM SYSDATE) 
FROM EMPLOYEE;
       
-- EMPLOYEE테이블에서 사원의 이름, 입사연도, 입사월, 입사일 조회

SELECT EMP_NAME, EXTRACT(YEAR FROM HIRE_DATE) 입사연도,
                 EXTRACT(MONTH FROM HIRE_DATE) 입사월, 
                 EXTRACT(MONTH FROM HIRE_DATE) 입사일
FROM EMPLOYEE; -- 선동일	1990	2	2


-- EMPLOYEE테이블에서 사원의 이름, 입사일, 근무년수 조회
-- 단, 근무년수는 현재연도 - 입사연도로 조회
SELECT EMP_NAME,HIRE_DATE, SYSDATE,
        EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) 근무년수
FROM EMPLOYEE; -- 선동일	90/02/06	22/03/19	32