SQL / Oracle 오라클 / 함수 정리 (문자/숫자/날짜/변환/조건/NULL)

---

SQL 단일행 함수 정리

1.문자  2.숫자  3.날짜  4.변환  5.조건  6.NULL

---

 

※ 오라클에서는 index가 1부터 시작

1. 문자 함수

SQL> select ename, length(ename)
  2  from emp;
  
where length(ename)>=6;

SQL> select initcap('oracle'), upper('oracle'), lower('ORACLE')
  2  from dual;

dual table은 임시로 함수 연산해볼수있는곳. 오라클 자체에서 제공된다.

 

select concat(concat(ename,'의 급여'),concat(sal,'만원'))

concat : 문자열 연결, 합치기

 

SQL> select instr('java programming','a',3,2) from dual;

 

instr : 특정문자의 위치를 반환
'a', 3, 2  :  3번째이후인거중에 2번째 'a'의 위치

 

SQL> select ename, substr(ename,2,1)

where substr(hiredate,1,2)=81;    # 입사일이 81년

where substr(ename,1,1)>'K' and
      substr(ename,1,1)<'Y'       # 이름의 첫글자가 'K'보다크고 'Y'보다 작은

substr : 문자의 일부분 반환
(ename,2,1) : 두번째 시작점부터 한글자3

MySQL 에서는 substring_index

 

SQL> select ename, replace(ename,'A','a') from emp;

replace : 특정문자 대체

 

SQL> select ename, lpad(ename,10,'*'),rpad(ename,10,'*')

lpad : 왼쪽부터 지정 문자로 채우는 함수

 

SQL> select 'ooracleoo',ltrim('ooracleoo','o'), rtrim('ooracleoo','o')

SQL> select trim(' 'from'     oracle     ')from dual;

ltrim : 왼쪽에서 특정문자 / 공백을 삭제 
rtrim : 오른쪽에서 삭제
trim : 앞뒤로 특정문자나 공백 삭제

 

like '_8A%’

like : 8A를 포함한 항목 검색하고싶을때
_ : 빈자리에 아무거나 한글자
% : 여기에는 아무 글자수나 상관없다는 뜻

 

---

2. 숫자 함수

SQL> select round(5.7589,0) from dual;
SQL> select ceil(10.6), floor(10.6) from dual;

 

SQL> select trunc(456.789,2) from dual;

trunc(456.789,2) : 456.789를 소수 둘째자리까지 절삭

 

SQL> select mod (10,3),mod(10,0) from dual; # 10을 3으로 나눈 나머지 1을 반환

mod : 나누기 연산 후 나머지 값을 반환

 

where sign(salary-15000)=1;

sign : 결과가 양수면 1, 음수면 -1, 0이면 0 반환

 

---

3. 날짜 함수

SQL> select sysdate-5, sysdate+5 from dual;              # 시스템상 오늘 날짜
SQL> select months_between(sysdate, hiredate)from emp;
SQL> select sysdate, add_months(sysdate,5)from dual;
SQL> select sysdate, next_day(sysdate,'금') from dual;   # 기준부터 다음 요일에대한 날짜
SQL> select sysdate, last_day(sysdate) from dual;        # 지정된 월의 마지막날짜

 

 

---

 

4. 변환 함수

SQL> select sal, to_char(sal,'L9,999') from emp;
to_char(hiredate,'YYYY')='2005'
SQL> select hiredate, to_char(hiredate,'mon') from emp;   # mon : 1월, 2월, 3월 이런식으로 출력

문자 반환 to_char : 숫자 / 날짜를 문자로.

L :  location의 화폐단위 출력(￦)
9,999 : 콤마 표시. 숫자 자리수는 무조건 9로 지정

 

SQL> select to_date('20200802','YYYYMMDD') from dual;    # 20/08/02 출력
SQL> select to_number('123')+100 from dual;              # 223 출력

날짜 반환 to_date,  숫자 반환 to_number

 

 

---

5. 조건 함수

SQL> select ename, sal, deptno, decode(deptno, 10,sal+2000,
  2                                            20,sal+3000,
  3                                            100000)
  4  from emp;

decode : 반드시 일치하는 조건 함수

(deptno, 10,sal+2000) : 조건: 컬럼값이 10이면 sal+2000으로 출력

 

SQL> select ename, sal,
  2  case when sal>=5000 then sal+100000
  3              when sal>=3000 then sal+200000
  4              else sal+300000
  5            end 보너스
  6  from emp;

code : 반드시 일치하지 않아도 범위 및 비교가능한 조건함수

end : 칼럼 제목 추가 then 뒤에 결과를 표시하는 컬럼. type통일해야함.

 

 

---

6. NULL 처리 함수

SQL> select ename, sal, comm, sal*12+nvl(comm,0)

nvl (comm,0) :  null을 0으로 바꾸어 연산하겠다는 의미

SQL> select ename, comm, deptno, nvl2(to_char(comm),to_char(deptno),'신입사원')

nvl2 : null인것 null아닌것 둘 다 정의
nvl2(to_char(컬럼명), Null인 경우deptno를 문자로 , Null 이 아닌 경우)