[SQL] 5. 반복을 최소화 하기 - 단일 행 함수 - 문자, 숫자, 날짜 함수

함수란?

• 쿼리를 작성하다 보면 자주 반복해서 사용하는 기능이나 코드가 너무 길어 매번 작성하기 어려운 기능들이 있습니다. 이런 기능들을 쉽게 사용하기 위해 사용하는 것이 함수 라는 개념입니다. 

• SQL 에서 사용할 수 있는 함수는 단일 행 함수와 복수 행 함수로 나누어집니다. 그 중 이번 포스팅에서 설명할 단일 행 함수는 단일 행에 대해서만 실행되며 행당 하나의 결과를 반환합니다. 단일 행 함수는 문자, 숫자, 날짜, 변환, 일반, 조건부 함수로 나눌 수 있으며 하나씩 살펴보겠습니다. 

 

함수 기본 구조 

function_name ( arg1, arg2 ..... )

• function_name 은 사용하는 함수의 이름이며, arg1, arg2 는 함수에 사용되는 각종 변수들입니다.

문자 함수 

• 대소문자 변환 함수

• 대소문자 변환 함수는 LOWER, UPPER, INITCAP 3개로 구성됩니다. 

• LOWER : 영문자를 소문자로 변환하는 함수입니다.

• LOWER('AbdceFG') -> abcdefg

• LOWER(ENAME) # 열이름을 인수에 넣는 것도 가능합니다.

• UPPER : 영문자를 대문자로 변환하는 함수입니다.
• UPPER('abcdEFG') -> ABCDEFG
• UPPER(ENAME) # 열이름을 인수에 넣는 것도 가능합니다.
• INITCAP : 영문자의 첫문자를 대문자로 변환하고 나머지 문자를 소문자로 변환하는 함수입니다.
• INITCAP('ABCDEFG') -> Abcdefg
• INITCAP(ENAME) # 열이름을 인수에 넣는 것도 가능합니다.
• 문자 조작 함수
• 문자 조작 함수는 CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, REPLACE 총 8개의 함수로 구성됩니다.
• CONCAT : 첫 번째 문자열 인수와 두 번째 문자열 인수를 연결하는 함수입니다. ' || ' 와 같은 기능 입니다.
• CONCAT('ABC','DEF') -> ABCDEF
• CONCAT(ENAME, JOB) # 열이름을 인수에 넣는 것도 가능합니다.
• CONCAT('ABC',ENAME) -> 'ABC' 와 ENAME 열이 연결됩니다.
• SUBSTR : 문자열에서 특정 위치에서 특정 개수 만큼의 문자들을 반환하는 함수입니다.
• SUBSTR('ORACLE', 2) -> RACLE
• SUBSTR('ORACLE', 2, 3) -> RAC
• SUBSTR(ENAME, 2) # 열이름을 인수에 넣는 것도 가능합니다.
• LENGTH : 문자열의 길이를 반환하는 함수입니다.
• LENGTH('ORACLE') -> 6
• LENGTH( SUBSTR('ORACLE', 2, 3) ) -> 3
• # 함수를 중첩해서 사용하는 것도 가능합니다.
• LENGTH(ENAME) # 열이름을 인수에 넣는 것도 가능합니다.
• INSTR : 문자열에서 특정 문자열의 위치를 숫자로 반환하는 함수입니다.
• INSTR('ORACLE ORACLE' , 'R') -> 2
• INSTR('ORACLE ORACLE' , 'R' , 6) -> 9
• INSTR('ORACLE ORACLE' , 'R' , 1, 2) -> 9
• INSTR( A , B , C , D ) - A는 찾아질 문자열이고 B 는 찾을 문자열 입니다. C 는 A 에서 검색 시작할 위치 이며, D 는 A 에서 B가 여러개 있을 경우 몇번째 B 의 위치를 반환할 것인가를 정하는 인수 입니다.
•  LPAD, RPAD : PAD 함수는 문자열의 길이를 맞추기 위해 사용하는 함수 입니다. 예를 들어 문자열의 길이가 5 일 때 10자리로 맞춰 주기 위해 사용하는 함수인데, LPAD 는 좌측에 부족한 길이를 채우고 RPAD 는 우측에 부족한 길이를 채워주는 함수 입니다.
• LPAD('ORACLE' , 10, '*') -> ****ORACLE
• RPAD('ORACLE' , 10, '*') -> ORACLE****
• TRIM, LTRIM, RTRIM : 문자열에서 맨앞이나 맨뒤, 혹은 양쪽 모두의 문자를 제거할 때 사용합니다. LTRIM 은 맨앞의 문자만 RTRIM 은 맨 뒤의 문자만 제거할 때 사용할 수 있습니다.
• TRIM(' ORACLE ') -> ORACLE
• TRIM(LEADING 'A' FROM 'ABABA') -> BABA
•    LTRIM('ABABA' , 'A') -> BABA
• TRIM(TRAILING 'A' FROM 'ABABA') -> ABAB
•    RTRIM('ABABA' ,'A') -> ABAB
• TRIM(BOTH 'A' FROM 'ABABA') -> BAB
• REPLACE : 문자열에서 특정 문자열을 찾아 원하는 문자열로 변경하는 함수 입니다.
• REPLACE('AAA', 'A', 'B') -> BBB
• LOWER, UPPER, INITCAP : 문자열의 대소문자를 변환하는 함수들 입니다.
• LOWER('ABC') -> abc
• UPPER('abc') -> ABC
• INITCAP('abC') -> Abc

숫자 함수 

• ROUND : 지정된 소수점 자릿수로 반올림하는 함수 입니다.
• ROUND( 4.555, 2 ) -> 4.56
• TRUNC : 지정된 소수점 자리수 미만을 버림 하는 함수 입니다.
• TRUNC( 4.555, 2 ) -> 4.55
• MOD : 나눈 나머지를 반환하는 함수 입니다.
• MOD( 16, 5 ) -> 1

날짜 함수 

• SYSDATE : 현재 데이터베이스 서버의 날짜 및 시간을 반환하는 함수 입니다. SYSDATE 는 열 이름을 사용하는 것처럼 사용합니다.
• SELECT SYSDATE FROM DUAL; -> 01-DEC-01
• MONTHS_BETWEEN : 두 날짜 간 개월 수를 반환하는 함수 입니다.
• SELECT MONTHS_BETWEEN(to_date('01-01-01', 'DD-MM-YY'), to_date('01-01-02', 'DD-MM-YY')) FROM DUAL; -> 12
• ADD_MONTHS : 날짜에 월을 추가할 때 사용하는 함수 입니다.
• SELECT ADD_MONTHS('01-01-01',1) FROM DUAL; -> 01/02/01
• NEXT_DAY : 지정된 날짜의 다음 요일을 반환하는 함수 입니다.
• SELECT NEXT_DAY(TO_DATE('19-12-17','DD-MM-YY') , 1) FROM DUAL; -> 17/12/24 (YY-MM-DD)
• 두번째 인덱스는 요일을 지정하는데 1이 일요일, 7이 토요일 입니다.
• LAST_DAY : 해당 월의 마지막 날을 반환하는 함수 입니다.
• SELECT LAST_DAY(TO_DATE('19-12-17','DD-MM-YY')) FROM DUAL; -> 17/12/31 (YY-MM-DD)
• ROUND : 날짜를 반올림 하는 함수 입니다.
• SELECT ROUND(TO_DATE('19-06-17','DD-MM-YY'), 'MONTH') FROM DUAL; -> 17/07/01 (YY-MM-DD)
• SELECT ROUND(TO_DATE('19-07-17','DD-MM-YY'), 'YEAR') FROM DUAL; -> 18/01/01 (YY-MM-DD)
• TRUNC : 날짜를 버림하는 함수 입니다.
• SELECT TRUNC(TO_DATE('19-06-17','DD-MM-YY'), 'MONTH') FROM DUAL; -> 17/06/01 (YY-MM-DD)
• SELECT TRUNC(TO_DATE('19-07-17','DD-MM-YY'), 'YEAR') FROM DUAL; -> 17/01/01 (YY-MM-DD)