06-25(금) 문자숫자날짜 함수

※ 개발 수업 정리글입니다. 꾸준히 정리할 예정이고 틀린 부분이 있다면 언제든지 댓글 환영입니다.

---

SQL 문자함수

자주 쓰이는 4가지
■ SELECT INITCAP ( ' 문자값 ' ) FROM DUAL ;  문자값의 첫 영어철자만 대문자 

ex) SELECT INITCAP (' abc ') FROM DUAL;        =Abc

■ UPPER LOWER 함수 
  ㄴUPPER는 (작은걸 크게) 소문자->대문자로, LOWER는 (큰걸 작게) 대문자->소문자로

■ SELECT CONCAT ( ' 문자값1 ' , ' 문자값2 ', .......) : 두개의 컬럼 값들을 하나로 출력

ex) SELECT CONCAT ( ' abc ' , ' def ' ) FROM DUAL;     =abcdef

=>이거 컬럼값 합치는 || 랑 헷갈릴 수 있는데 ||는 컬럼값을 합치는거고, CONCAT은 단순 문자를 합치기!

   ㄴ> 하지만 CONCAT과 || 같은 용도로도 활용은 가능. * oracle은 인자 2개만 가능. 그외 DB는 3개 이상 인자 가능.

   ㄴ> oracle에서 쓸거면 CONCAT( 인자1, CONCAT(인자2, 인자3) ) 또는 || 로 묶어서 써야함.

 

■ SELECT LENGTH ( ' 문자값1 ' )     : 문자값의 철자갯수

ex) SELECT LENGTH ( ' apple ' ) FROM DUAL ;        = 띄어쓰기 포함 7

SELECT ENAME, LENGTH(ENAME) FROM EMP;   EMP테이블에서 [ENAME] 컬럼과 [ENAME의 철자갯수] 컬럼을 보여라

 

 

 

■ INSTR 특정값의 위치값

SELECT INSTR ( '문자값' , '문자값에서 찾을 값' , 몇번째부터 시작해서 , 몇번째 값 ) FROM DUAL ;

ex) SELECT INSTR ( 'banana', 'a', 1 , 3 ) FROM DUAL;    =6

=>banana에서 a를 찾을 건데 (3개쟌? 그중) 1번째로 나오는 a에서 부터 3번째 a의 위치는 6이라는것

근데, 시작이 2면? ( 'banana', 'a', 2, 3 ) 이면? 이것도 6 

' ' 로 하면 공백철자가 있는거니까 절대 ' banana ' 이렇게 쓰면 안된다! 꼭 'banana' !!!!

일치하는 게 없어도 0, 오류여도 0으로 나오니까!

-이렇게도 가능

SELECT INSTR ( 'banana', 'a', 1 , 3 ) , INSTR ( 'banana', 'n', 1 , 2 ) FROM DUAL; 

---

■ ★★SUBSTR ( '문자', *번째 자리에서, *개를 꺼내옴)

ex) SELECT SUBSTR ( '900303-1234567', 8, 1) FROM DUAL ;

900303-1234567의 8번째 자리에서 1개를 꺼내옴 = 1

ㄴ( '900303-1234567', 9, 2 )  =23

ㄴ( '900303-1234567', 11,3 ) =456

- *개를 지정하지 않으면, *번째 자리에서부터 끝까지 모든 문자열을 쭉 보여줌

ㄴ( '900303-1234567', 10 )   =34567 

ex) SELECT HIREDATE, SUBSTR(HIREDATE, 1,2) FROM EMP;

=>무슨 뜻일까? [입사일] 컬럼과 [입사일자 값의 1번째 자리에서 2개] 컬럼을 보여라= 입사일, 입사년도

- * 번째 자리가 -숫자 음수값이면, 뒤에서부터 숫자번째인데 거기부터 끝까지 반환

ex)  SELECT SUBSTR( '900303-1234567', -10 )   FROM DUAL;   =03-1234567

뒤에서 10번째부터 끝까지 반환  

 

**나중에 java에서 substr함수는 1부터 아니고 0부터 시작임..

---

■ REPLACE (단어, 얘를, 얘로)

SELECT REPLACE( 'JACK and JUE' , 'J' , 'BL' ) FROM DUAL;  =BLACK and BLUE

SELECT ENAME,JOB, REPLACE(JOB, 'A', 'S' ) FROM EMP;

 

■ LPAD ( '오른정렬될 단어' , 단어보다 긴 전체글자수, '삽입할 문자')  =RPAD는 반대로

SELECT LPAD ( 'HUNGRY' , 9, 'SO ' ) FROM DUAL;

RPAD ( '왼쪽정렬될 단어' , 단어보다 긴 전체글자수, '삽입할 문자')

 

 

 

★주민번호 뒷자리 성별 이후부터 개인정보법 보호로 *로 반환해야한다면?★
① SUBSTR *번째 순서에서 *개를 꺼내오는 함수 활용
SELECT SUBSTR( '900303-1234567' , 1, 8) || '******' 주민번호 FROM DUAL;
주민번호 첫번째부터 8개 보여주고, *6개랑 합치고, 컬럼별칭은 주민번호

② SUBSTR로 보여줄 부분만 추출하고,  나머지 *는 RPAD로 채워넣기
SELECT RAPAD(SUBSTR('900303-1234567',1,8),14,'*')FROM DUAL;
주민번호 오른정렬로 첫번째부터 8개 보여주고, 총14글자 만들건데, 빈 곳은 *로, 별칭은 주민번호

③ SUBSTR로 보여줄 부분만 추출하고, REPLACE로 바꿔주기
SELECT REPLACE('900303-1234567', SUBSTR('900303-1234567', 9) , '*****') 주민번호 FROM DUAL; 
주민번호에서, SUBSTR 9번째부터 끝까지 추출된 것이, *로 보이게 바꿔라

■ LTRIM ( 단어, 왼쪽에서부터 *란 철자로 시작해서 아닌 철자 나올때까지 *란 철자 삭제지정)

SELECT LTRIM('BBANANA', 'B') FROM DUAL;

RTRIM은 같고 오른쪽에서부터 

※ 삭제지정문자를 안쓰면, 단어의 왼쪽(left trim이니까) 부터 공백이 사라짐  

SELECT LTRIM ( '    BBANANA     ' ) = [BBANANA    ]

SELECT RTRIM ( '    BBANANA     ' ) = [    BBANANA]

 

졸려 죽겠다야아ㅏ아ㅏ아

 

■ ★★★TRIM★★★

좌우 양쪽 모두 삭제할때 사용, LTRIM RTRIM 굳이 안쓰고 얘 바로 써도 됨

SELECT TRIM( LEADING '삭제할 거' FROM '단어'나 컬럼표현식)   이끄는걸 삭제

SELECT TRIM( TRAILING '삭제할 거' FROM '단어'나 컬럼표현식)   따라가는걸 삭제

SELECT TRIM(   [BOTH]  '삭제할 거' FROM '단어'나 컬럼표현식)   양쪽 삭제 , 아무것도 없는 기본값은BOTH로

 

 

 

 

***웤샵***

1번 문제.

내답,정답) SELECT CATEGORY 계열,DEPARTMENT_NAME 학과이름, CAPACITY 정원
FROM TB_DEPARTMENT
WHERE CATEGORY='공학'
AND CAPACITY BETWEEN 20 AND 30
ORDER BY 2;

 

---

SQL  숫자함수

■ ROUND (숫자, +양수면 그 소수점 이후를 반올림 -음수면 그 정수자리 이후를 반올림 [기본값은 0이니까 0이후, 즉 소수점 첫째에서 정수 첫번째로 반올림] )

SELECT ROUND( 456.789 , 2) FROM DUAL;  =456.79

SELECT ROUND( 456.789     ) FROM DUAL; =457

SELECT ROUND( 456.789 , -1) FROM DUAL; =460

 

■ ★TRUNC(숫자, +양수면 그 소수점 이후 삭제 -음수면 그 정수자리 이후 삭제  [기본값은 0이니까 0이후, 즉 소수점 첫째부터 삭제] )

SELECT TRUNC( 456.789 , 2) FROM DUAL;  =456.78

SELECT TRUNC( 456.789     ) FROM DUAL; =456

SELECT TRUNC( 456.789 , -1) FROM DUAL; =450

 

■ mod(숫자, 나눌 숫자)  = 결과값이 나머지

★★★홀수 짝수 판단 가능,

나머지가 0이면 짝수, 나머지가 1이면 홀수

 

 

 

 

 

ex) SELECT EMPNO, ENAME, SAL FROM EMP WHERE MOD(EMPNO,2)=1 ;

사번 나누기2의 나머지가 1(=사번이 홀수인)인, [사번] 컬럼 [사원명] 컬럼 [월급] 컬럼을 보여라

 

■  CEIL 무조건 올림, FLOOR 무조건 내림

SELECT    CEIL(10.3) ,   CEIL(-10.8) FROM DUAL;   =11,-10

SELECT FLOOR(10.8), FLOOR(-10.3) FROM DUAL;  =10, -11

 

■ SIGN(양수면:1 음수면:-1 0이면: 0)

SELECT SIGN(100), SIGN(0), SIGN(-20) FROM DUAL;  = 1, 0, 1

ex) SELECT EMPNO, ENAME, SAL FROM EMP

WHERE SIGN(SAL-1500)=1 ;

월급에서 1500을 뺀 값이 1 양수인 사람(즉 월급 1500보다 많이 받는)것의, [사번 사원명 월급]컬럼으로 보여라

=WHERE SAL>1500 이지만 SIGN 쓰는 방법도 알아둬야함

 

---

SQL 날짜 함수

■ SYSDATE   DB에 있는 날짜 반환(oracle / mysql =now(), mssql= GETDATE() )

=>쇼핑몰 회원가입일 기록 가능,  D-Day 같은거 만들수 있다함

[기준 21/06/28]

SELECT SYSDATE +7 FROM DUAL;  =21/07/05  --일주일 후

SELECT SYSDATE -7 FROM DUAL;  =21/06/21  --일주일 전

SELECT SYSDATE -날짜 FROM DUAL;   뭐여 이건?

SELECT SYSDATE +7/24 FROM DUAL; 얘도 뭐지?

 

①오늘부터 100일후는 며칠? 답

SELECT SYSDATE+100 FROM DUAL; = 21/10/03

 

②사원들의 근속년도 구하기

SELECT ENAME, HIREDATE, TRUNC((SYSDATE-HIREDATE)/365) 년 FROM EMP ORDER BY 3 DESC;

오늘-입사일 빼면 근속일수 ****일이 나오고 근속일수 ****일/365일= 근속년도 나온 값의 소수점은

삭제하고 해당컬럼 별칭은 '년'이고 이 컬럼을 내림차순으로 해서, [사원명 입사일 근속년도]컬럼을 보여라

■ MONTHS_BETWEEN 월과 월 사이의 개월수,   ADD_MONTH 오늘부터 몇개월 후 날짜

-근속월수 구하기

SELECT ENAME, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) "근속월수" FROM EMP ORDER BY 3 DESC;

사원명 입사일 [오늘부터 입사일 사이의 개월수에서 절삭한 정수의 컬럼의 별칭] 근속월수인데, 3번째 순서인 근속월수를 내림차순으로 보여라

 

-입사하고 3개월 후

SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE,5) FROM EMP ORDER BY 3 DESC;

사원명 입사일 [입사일에서 5개월 후 날짜] 을 보이는데, 3번째 5개월후 날짜를 내림차순으로

■ NEXT_DAY 돌아오는 가장 가까운 요일을 날짜로 변환

SELECT ENAME, HIREDATE, NEXT_DAY(HIREDATE, '금'), NEXT_DAY(HIREDATE, 6) FROM EMP ORDER BY 3 DESC;

  일 월 화 수 목 금 토 일

= 1  2  3   4  5  6  7   8  로도 바꿔쓰기 가능

 

■ LAST_DAY 그 날짜가 속한 달의 마지막 날짜를 반환

SELECT ENAME, HIREDATE, LAST_DAY(HIREDATE) FROM EMP ORDER BY 2 DESC;

 

 
■ 원래 ROUND 함수는 반올림 함수지만 날짜도 반올림함

YEAR     16/05/01 -> 16/01/01   

            16/11/15 -> 17/01/01

MONTH  16/07/24 -> 16/08/01

            16/11/15 -> 16/11/0

■ 원래 TRUNC 함수는 절삭 함수지만 날짜도 절삭함

YEAR     16/05/01 -> 16/01/01

            16/12/01 -> 16/01/01

MONTH  16/05/31 -> 16/05/01

---

★★변환 함수 매우 중요중요중요★★★