TIL_210413_SQL활용(숫자 함수, 날짜 함수, 문자처리 함수, 형변환 함수...)

목차

숫자함수
1) ABS
2) FLOOR
3) ROUND
4) TRUNC
5) MOD

문자처리함수
1)대소문자 변환함수
(1) UPPER
(2) LOWER
(3) INITCAP

문자 길이 구하는 함수
1) LENGTH
2) LENGTHB

문자 조작 함수
1) CONCAT
2) SUBSTR
3) SUBSTRB
4) INSTR
5) INSTRB
6) LPAD
7) RPAD

형변환 함수
1) TO_CHAR
2) TO_DATE
3) TO_NUMBER

날짜함수
1) SYSDATE
2) TRUNC
3) MONTHS_BETWEEN
4) ADD_MONTHS
5) LAST_DAY

NULL을 다른 값으로 변환하는 함수
1) NVL
2) NVL2
DECODE 

 

 

 

 

 

 

DUAL 테이블 

간단한 연산결과 등을 구할 때 사용하는 임시적인 테이블 ( = dummy 테이블)

DUAL 테이블은 select문에서만 사용 가능하며 insert 구문에는 불가하다

 

dual 테이블 예시

---

숫자함수

1) abs(숫자)

절대값 구하는 숫자 함수

 

2) floor(숫자)

소수점 아래 숫자들을 버리는 숫자 함수

 

3) ROUND(숫자, 원하는 자리수)

특정 자리수에서 반올림하는 숫자 함수

 

예) 소숫점 첫째자리에서 반올림 

    select 34.567, round(34.567) from dual;

 

예) 소숫점 셋째자리에서 반올림

    select 34.567, round(34.567, 2) from dual;

 

예) 1의 자리에서 반올림 

    select 34.567, round(34.567, -1) from dual;

 

ROUND 함수 예시

 

4) TRUNC(숫자, 특정자릿수)

특정 자릿수에서 잘라내는 숫자 함수

 

 

5) MOD(숫자, 몫)

나머지를 구하는 숫자 함수

 

---

 

 

문자 처리 함수

1)대소문자 변환함수

(1) UPPER(문자)

문자를 모두 대문자로 변환

 

(2) LOWER(문자)

문자를 모두 소문자로 변환

 

(3) INITCAP(문자)

각 단어의 첫글자만 대문자로, 나머지는 소문자로 변환

 

---

 

 

문자 길이 구하는 함수

1) LENGTH(문자)

문자 길이 반환 (영어 1byte, 한글 1byte)

 

2) LENGTHB(문자)

문자 길이 반환 (영어 1byte, 한글 3byte)

 

---

 

문자 조작 함수

1) CONCAT(문자1, 문자2)

문자 값을 연결 (2개만 가능, 3개 이상은 X)

 

 

2) SUBSTR(대상, 시작번호, 갯수 (byte 기준) )

원하는 문자열만큼 잘라서 반환 (한글 1byte, 오라클에선 문자열의 first char index = 1 로 자바와는 다르다)

 

 

3) SUBSTRB(대상, 시작번호, 갯수 (byte 기준) )

바이트 수를 기준으로 문자열 일부만 추출하여 반환 (한글 3byte)

 

substr, substrb 비교 예시 

 

4) INSTR(대상, 찾을 문자, 시작위치, 몇번째 발견)

특정 문자의 위치값 반환 

 

 

5) INSTRB(대상, 찾을 문자, 시작위치, 몇번째 발견)

바이트 수 기준으로 특정 문자의 위치값 반환 (한글 3BYTE)

 

INSTR, INSTRB 비교 예시 

 

6) LPAD(값, 총문자길이, 채움문자)

지정한 길이만큼 왼쪽부터 특정문자로 채우는 함수

 

7) RPAD(값, 총문자길이, 채움문자)

지정한 길이만큼 오른쪽부터 특정문자로 채우는 함수

 

---

 

 

형변환 함수

1) TO_CHAR(날짜데이터, '출력양식')

날짜를 문자형으로 변환

 

예) 입사 날짜, 오늘날짜 + 요일 출력 

 

예) 오늘날짜 현재 시간 출력

 

 

2) TO_DATE('문자', '포맷')

문자를 날짜형으로 변환

 

예) select ename, hiredate from emp
    where hiredate = TO_DATE(20070402, 'YYYYMMDD');   

    => hiredate는 날짜형이기에 문자열을 날짜형으로 바꿔줘야함

 

 

예) 해당 날짜를 기준으로 며칠이나 지났는지 출력 

 

3) TO_NUMBER(문자, 숫자타입)

문자를 숫자형으로 변환

 

예)  select '20,000'-'10,000' from dual; => 문자 - 문자이므로 연산 불가 (형변환 필요)

=> select TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '99,999') from dual;

    ( 99,999는 각자리수가 가질 수 있는 최대값 작성 )

 

---

 

 

날짜 함수

1) SYSDATE

현재날짜 반환 

 

 

2) TRUNC(date, format)

특정 조건을 기준으로 버리는 함수

 

예) 사원들의 입사월까지만 출력

 

3) MONTHS_BETWEEN(date1, date2)

두날짜 사이의 간격 달수를 구하는 함수

 

예) 사원 이름, 오늘날짜, 입사일, 근무달수 출력

 

 

4) ADD_MONTHS(date, number)

개월수를 더하는 함수 

 

예) 사원명, 입사날짜, 입사 후 6개월 날짜 출력

 

 

5) LAST_DAY(date)

해당 달의 마지막 날짜를 반환

 

예) 사원명, 입사일, 입사한 달의 마지막 날짜 출력

 

---

 

 

NULL을 다른 값으로 변환하는 함수

1) NVL(expr1, expr2)

수치 데이터를 사용한 NVL(comm, 0)
날짜 데이터를 사용한 NVL(hiredate, TO_DATE('2015/03/10', 'YYYY/MM/DD')
문자 데이터를 사용한 NVL(job, '매니저')

 

예) 사원들의 연봉 출력 

select  ename, sal, comm, deptno, sal*12+comm
from  emp
order by  deptno;

=> 커미션이 널값이면 계산에서 배제되므로 sal*12+comm 도 null값으로 바뀜, 따라서 0으로 바꿔줘

 

 

 

수정후

select  ename, sal, deptno, NVL(comm, 0), sal*12 + NVL(comm,0)
from  emp
order  by deptno;

 

 

2) NVL2(expr1, expr2, expr3)  (expr1이 대상필드)

expr1의 결과가 null이 아니면 expr2를 반환
expr1의 결과가 null이면 expr3을 반환 

(NVL보다 결과 도출 시간 단축)

 

예)

select  ename, sal, deptno, NVL2(comm, sal*12+comm, sal*12)
from  emp
order by  deptno;

 

 

---

DECODE (= SWITCH문과 유사)

 

형식

DECODE(표현식, 조건1, 결과1,
               조건2, 결과2,
               조건3, 결과3,
               기본결과) 

(조건수는 더 추가할 수 있으며 기본결과 생략 가능)

 

예) 사원명, 소속부서 번호, 소속부서명 출력