[SQL] FUNCTION

21 Jul 2018

Reading time ~4 minutes

함수

기능의 프로그램

문자 함수

lower(), upper(), initcap() 대소문자를 변환시키는 함수

lower(): 컬럼의 모든 문자를 소문자로 바꾸는 함수
upper(): 컬럼의 모든 문자를 대문자로 바꾸는 함수
initcap(): 컬럼의 첫 문자를 모두 대문자로 바꾸는 함수

SELECT lower(last_name), upper(last_name), initcap(last_name)
FROM employees;

주의
- 비교 방식이 INDEX SCAN 에서 FULL SCAN 으로 바뀜(I/O가 많이 발생 -> 악성코드)
- WHERE 조건절에 위 함수를 쓰지 말자! 입력 단위에서 사용을 지향하자!

concat() 2개의 컬럼을 연결시키는 함수

SELECT last_name || first_name
FROM employees;

SELECT concat(last_name,first_name)
FROM employees;

length() 문자의 길이를 알려주는 함수

SELECT last_name, length(last_name)
FROM employees;

substr() 문자를 추출하는 함수

Ex. 앞에서부터 3개 추출

SELECT last_name, substr(last_name,1,3)
FROM employees;

Ex. 뒤에서부터 3개 추출

SELECT last_name, substr(last_name,-1,3)
FROM employees;

lpad(),rpad() 문자의 포멧을 정하는 함수

lpad(A,B,C): A를 B자리로 고정시키고 왼쪽의 공백을 놔두지 말고 C 입력
- 특정 값이 8자리라면, 2자리만 *처리가 됌

SELECT lpad(1234,8,'*')
FROM dual;

>****1234

rpad(A,B,C): A를 B자리로 고정시키고 오른쪽의 공백을 놔두지 말고 C 입력

SELECT rpad(1234,8,'*')
FROM dual;

>1234****

instr() 문자의 위치를 찾는 함수

특정 글자가 특정 컬럼 안에 들어있는지 확인할 때 유용한 함수
0 이 아닌 값이 나오면 특정 글자가 들어 있다고 판단할 수 있음
1부터 시작해 1번째 c가 있는 위치

SELECT instr('abbcabbc','c',1,1)
FROM dual;

>4


SELECT instr('abbcabbc','c') --(1,1)은 생략 가능
FROM dual;

>4

참고
- dual은 Dummy table로 가상의 테이블을 만들어 쿼리결과를 확인 할 수 있음

SELECT last_name, instr(last_name,'i')
FROM employees;

instr() 활용

아래 2개는 같은 쿼리 결과
성능은 아래 것이 더 좋음 (함수는 병렬처리하기 때문)

SELECT last_name
FROM employees
WHERE last_name like '%i%';

SELECT last_name
FROM employees
WHERE instr(last_name,'i')>0;

last_name에 a문자가 2개 이상 포함되어 있는 경우도 instr()를 사용하여 풀 수 있다.: instr(last_name,'a',1,2)

1번째 위치에 a가 있는 경우를 찾겠다: instr(last_name,'a',1,1)=1

trim(), ltrim(), rtrim() 연속된 특정 문자를 제거하는 함수

trim(): 접두 접미 부분의 연속된 특정 문자를 제거하는 함수
ltrim(): 왼쪽(접두) 부분의 연속된 특정 문자를 제거
rtrim(): 오른쪽(접미) 부분의 연속된 특정 문자를 제거

SELECT trim('A' from 'AbBBCAA')
FROM dual;

>bBBC

SELECT ltrim('AABBCA','A')
FROM dual;

>BBCA

SELECT rtrim('ABBCA','A')
FROM dual;

>ABBC

replace() 문자를 다른 문자로 치환하는 함수

Ex. -를 %로 바꿈

SELECT replace('100-001','-','%')
FROM dual;

>100%100

Ex. 공백을 제거할 때

SELECT replace(' 0 0 0 1 2 ',' ','')
FROM dual;

>00012

숫자함수

round() 반올림해서 소수점을 버리는 함수

SELECT round(45.926,0), round(45.926,1), round(45.926,2), round(45.926,-1), round(45.926,-2)
FROM dual;

>45.9 46 45.93 50 0

trunc() 내림 함수

SELECT trunc(45.926,0), trunc(45.926,1), trunc(45.926,2), trunc(45.926,-1), trunc(45.926,-2)
FROM dual;

>45 45.9 45.92 40 0

mod() 어떠한 값으로 나누었을 때 몫이 아닌 나머지 값을 주는 함수

짝수/홀수, 윤달 확인 등을 확인할 때 유용함

SELECT mod(13,2)
FROM dual;

>1

ceil() 소수점 있을 때(소수점이 0을 초과했을 때), 무조건 올림 하는 함수

SELECT ceil(45.0), ceil(45.000), ceil(45.001), ceil(45.926)
FROM dual;

>45 45 46 46

날짜 함수

날짜 계산하는 방법

sysdate: Server 시간을 return
1. 날짜 +(-) 숫자(일수) = 날짜
2. 날짜 - 날짜 = 숫자(일수)
3. 날짜 + 시간/24 (24시간 기준) = 날짜
  날짜 + 분/(24x60) = 날짜
  날짜 + 초/(24x60x60) = 날짜
4. 날짜 + 날짜 = 오류

Ex.1

SELECT sysdate, sysdate+7, sysdate-7
FROM dual;

>18/05/28 18/06/04 18/05/21

Ex.2 근무 일수 파악 

SELECT employee_id, sysdate-hire_date
FROM employees;

months_between() 두 날짜 사이의 달 수를 계산해주는 함수

두 날짜는 반드시 Date type 이여야 함

Ex.

SELECT months_between(to_date('2018-05-24','yyyy-mm-dd'),
to_date('2018-11-22','yyyy-mm-dd'))
FROM dual;

>-5.93548


SELECT months_between(to_date('2018-11-22','yyyy-mm-dd'),
to_date('2018-05-24','yyyy-mm-dd'))
FROM dual;

>5.93548

add_months() 달 수를 더하는 함수

SELECT sysdate, add_months(sysdate, 6)
FROM dual;

>18/05/28 
>18/11/28

next_day() 특정 날짜를 기준으로 가까운 미래의 시점의 날짜 정보를 파악하는 함수

예를 들어, sysdate(현재)를 기준으로 금요일의 정보를 알 수 있다.
날짜는 언어와 지역에 종속되기 때문에 Friday 대신 금요일을 입력해야 함

Ex.

SELECT sysdate, next_day(sysdate, '금요일')
FROM dual;

>18/05/28
>18/06/01

last_day() 특정 날짜의 마지막 일을 알 수 있는 함수

SELECT last_day(sysdate)
FROM dual;

>18/05/31

round() 달을 기준으로 반올림 함

함수 이름이 같아도 입력 인수 값에 따라 다른 함수로 사용됌(overload 기능)

Ex.

SELECT sysdate, round(sysdate, 'month')
FROM dual;

>18/05/28
>18/06/01

--Day를 통해 month를 반올림함

Ex.

SELECT sysdate, round(sysdate, 'year')
FROM dual;

>18/05/28 
>18/01/01

--연도를 기준으로 달을 봄(sysdate가 7월이였으면 19년도가 나옴)

trunc() 특정 날짜의 1일을 알려주는 함수

SELECT sysdate, trunc(sysdate, 'month'), trunc(sysdate, 'year')
FROM dual;

>18/05/28 
>18/05/01
>18/01/01

NULL 함수

NVL() null 값을 대체하는 함수

NVL(): null 값을 다른 값으로 대체하는 함수
NVL(A,B) 경우 A와 B의 형을 일치시켜야함

SELECT last_name, salary, commission_pct,
      (salary*12)+(salary*12*commission_pct), --NULL값이 포함되어 있음
      (salary*12)+(salary*12*NVL(commission_pct,0)) --NULL값이 있으면 0으로 대체해서 출력
FROM employees;

SELECT employee_id, NVL(commission_pct,'no comm') --입력 데이터의 형태가 달라 오류 발생
FROM employees;

SELECT employee_id, NVL(TO_CHAR(commission_pct),'no comm') --형을 일치 시켜야함
FROM employees;

NVL2() null을 조건에 따라 대체하는 함수

3개의 인자 값이 필요
NVL2(A,B,C): A가 NULL이 아니면 B를 수행하고 NULL이면 C를 사용
2번째 인수값과 3번째 인수값의 형을 일치시키도록 하자

SELECT employee_id, 
NVL2(commission_pct, (salary*12)+(salary*12*commission_pct),salary*12)
FROM employees;

COALESCE() null이 아닌 값을 계속 찾아 가는 함수

인수값의 고정이 없음
NVL() 함수를 일반화 시킨 함수
1번째 인수값이 NULL이면 2번째, 2번째 인수값이 NULL이면 3번째… 방식으로 NULL이 아닌 값을 찾아감

SELECT employee_id, 
COALESCE((salary*12)+(salary*12*commission_pct), salary*12, salary)
FROM employees;

NULLIF() null을 만드는 함수

NULL(A,B): A와 B의 값이 같으면 NULL, 다르다면 A 출력
NULL을 다른 값으로 대체하는 함수는 아님

SELECT employee_id, NULLIF(length(last_name),length(first_name))
FROM employees;

IS NULL 연산자 -NULL이 들어 있는 row 출력

SELECT *
FROM employees
WHERE commission_pct is null;

IS NOT NULL 연산자 -NULL이 없는 row 출력

SELECT *
FROM employees
WHERE commission_pct is not null;