[SQL] 함수

2022.08.25 04:42

📁 함수

특정 값을 읽어서 계산한 결과를 리턴

  • 단일 행 함수
    N개의 값을 읽어서 N개의 결과값 리턴
    - 문자 관련 함수, 숫자 관련 함수, 날짜 관련 함수, 형변환 함수
  • 그룹 함수
    N개의 값을 읽어서 1개의 결과 리턴

단일 행 함수와 그룹 함수는 같이 쓸 수 없음
Result Set에 올라갈 행의 갯수가 다르기 때문

📁 단일 행 함수

1) 문자 관련 함수

LENGTH

문자열 길이 반환

SELECT LENGTH('ABC') FROM DUAL;		-- 3
SELECT LENGTH('오라클') FROM DUAL;	-- 3

LENGTHB

문자열의 바이트수 반환

SELECT LENGTHB('ABC') FROM DUAL;	-- 3
SELECT LENGTHB('오라클') FROM DUAL;	-- 9

INSTR

해당 문자열의 위치 반환
인덱스는 1부터 시작되며 문자열이 없으면 0 반환

SELECT INSTR('12345678', '2') FROM DUAL;		-- 2
SELECT INSTR('123454321', '4', -1) FROM DUAL;	-- 6

LPAD, RPAD

문자열의 길이를 지정하고 좌우로 남은 공간은 지정한 문자열로 치환

SELECT LPAD('12345', 10) FROM DUAL;			--      12345
SELECT RPAD('12345', 10, '#') FROM DUAL;	-- 12345#####

LTRIM, RTRIM

각각 좌우측의 공백 혹은 지정 문자를 삭제

SELECT LTRIM('  AA') FROM DUAL;					-- AA
SELECT LTRIM('3210AA0123', '0123') FROM DUAL;	-- AA0123
SELECT RTRIM('  AA  ') FROM DUAL;				-- AA
SELECT RTRIM('3210AA0123', '0123') FROM DUAL;	-- 3210AA

TRIM

LTRIM, RTRIM과 기능적으로 유사하지만 형식이 다르고 오직 하나의 문자만 지울 수 있음

  • LEADING : 왼쪽 문자만 지움
  • BOTH : 양쪽 문자를 모두 지움
SELECT TRIM('   AA   ') FROM DUAL;						-- AA
SELECT TRIM(LEADING 'A' FROM 'ABBAABBA') FROM DUAL;	-- BBAABBA
SELECT TRIM(BOTH 'A' FROM 'ABBAABBA') FROM DUAL;		-- BBAABB

SUBSTR

문자열의 일부를 반환

SELECT SUBSTR('123456789', 7) FROM DUAL;		-- 789
SELECT SUBSTR('123456789', 5, 2) FROM DUAL;		-- 56
SELECT SUBSTR('123456789', 5, 0) FROM DUAL;		-- NULL
SELECT SUBSTR('123456789', -8, 3) FROM DUAL;	-- 234

LOWER, UPPER, INITCAP

문자열을 소문자, 대문자, 혹은 단어 단위로 변환

SELECT LOWER('ABC DEF') FROM DUAL;		-- abc def
SELECT UPPER('abc def') FROM DUAL;		-- ABC DEF
SELECT INITCAP('abc DEF') FROM DUAL;	-- Abc Def

CONCAT

문자열과 문자열을 이어붙임

SELECT CONCAT('가나다라', '1234') FROM DUAL;	-- 가나다라1234

REPLACE

지정된 문자열을 다른 문자열로 대체

SELECT REPLACE('서울시 강남구 역삼동', '역삼동', '삼성동') FROM DUAL;	-- 서울시 강남구 삼성동

2) 숫자 관련 함수

ABS

절댓값 반환

SELECT ABS(-1) FROM DUAL;	-- 1

MOD

두 값을 나눈 나머지. 부호는 나누어지는 수의 것을 따라감

SELECT MOD(10, 3) FROM DUAL;	-- 3
SELECT MOD(-10, 3) FROM DUAL;	-- -3

ROUND

인자를 반올림해서 반환. 두번째 인자는 올림할 자리

SELECT ROUND(123.456) FROM DUAL;	-- 123
SELECT ROUND(123.456, 1) FROM DUAL;	-- 123.5

CEIL, FLOOR

올림, 버림

SELECT CEIL(1234.56) FROM DUAL;		-- 1235
SELECT FLOOR(1234.56) FROM DUAL;	-- 1234

TRUNC

절삭. 수학적 의미의 버림과는 다름

SELECT TRUNC(-1234.56) FROM DUAL;	-- -1234
SELECT TRUNC(1234.56, 1) FROM DUAL;	-- 1234.5

3) 날짜 관련 함수

SYSDATE

현재 날짜를 반환

SELECT SYSDATE FROM DUAL;

MONTHS_BETWEEN

두 날짜의 시간차를 개월수로 반환
앞에 인자가 최근 날짜여야 양수로 반환

SELECT MONTHS_BETWEEN(SYSDATE, BIRTHDAY) FROM PERSON;

ADD_MONTHS

날짜에 지정한 개월수를 더함

SELECT ADD_MONTHS(SYSDATE, 2) FROM DUAL;

NEXT_DAY

날짜에서 가장 가까운 지정한 요일의 날

SELECT SYSDATE, NEXT_DAY(SYSDATE, '목요일') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '목') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '5') FROM DUAL;

LAST_DAY

날짜의 달에서 마지막 일

SELECT LAST_DAY(SYSDATE) FROM DUAL;

EXTRACT

날짜에서 년, 월, 일을 추출

SELECT EXTRACT(YEAR FROM SYSDATE),
	EXTRACT(MONTH FROM SYSDATE),
    EXTRACT(DAY FROM SYSDATE)
FROM DUAL;

4) 형변환 함수

TO_CHAR

숫자형, 날짜형을 문자형으로 변환

  • 숫자→문자
    숫자를 여러 종류의 문자열로 표현
SELECT TO_CHAR(1234) FROM DUAL;
SELECT TO_CHAR(1234, '99999') FROM DUAL;	--  1234
SELECT TO_CHAR(1234, '00000') FROM DUAL;	-- 01234

SELECT TO_CHAR(1234, 'FML99,999') FROM DUAL;	-- ₩1234
SELECT TO_CHAR(1234, 'FM$99,999') FROM DUAL;	-- $1,234
  • 날짜→문자
    날짜를 여러 종류의 문자열로 표현
SELECT
	TO_CHAR(SYSDATE, 'YYYY'),		-- 2022
    TO_CHAR(SYSDATE, 'YY'),			-- 22
    TO_CHAR(SYSDATE,'YEAR'),		-- TWENTY TWENTY-TWO
	
    TO_CHAR(SYSDATE, 'MM'),			-- 08
    TO_CHAR(SYSDATE, 'MON'),		-- 8월
    TO_CHAR(SYSDATE, 'MONTH'),		-- 8월
    TO_CHAR(SYSDATE, 'RM'),			-- VIII

	TO_CHAR(SYSDATE, 'DDD'),		-- 1년 기준
    TO_CHAR(SYSDATE, 'DD'),			-- 1달 기준
    TO_CHAR(SYSDATE, 'D'),			-- 1주 기준

	TO_CHAR(SYSDATE, 'Q'),			-- 분기
    TO_CHAR(SYSDATE, 'DAY'),		-- 요일
    TO_CHAR(SYSDATE, 'DY'),			-- 요일
    
    TO_CHAR(SYSDATE, 'AM')			-- 오전/오후 중 적절한 값
	TO_CHAR(SYSDATE, 'PM')			-- 오전/오후 중 적절한 값
    TO_CHAR(SYSDATE, 'HH'),			-- 시(12)
    TO_CHAR(SYSDATE, 'HH24'),		-- 시(24)
    TO_CHAR(SYSDATE, 'MI'),			-- 분
    TO_CHAR(SYSDATE, 'SS')			-- 초
FROM DUAL;
  • 날짜→형식화된 문자열
    원하는 형식으로 날짜 출력. 형식에 문자열이 들어갈 경우 ""로 감싸주어야
-- 2022.08.16(화) 오후 05:45:40
SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD(DY) AM HH:MI:SS') FROM DUAL;

-- 2022년 08월 16일 화요일 오후 05시 45분 40초
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" DAY AM HH"시" MI"분" SS"초"') FROM DUAL;

TO_DATE

문자나 숫자를 날짜 데이터로 변환

SELECT TO_DATE(20220818) FROM DUAL;
SELECT TO_DATE(20220818, 'YYYYMMDD') FROM DUAL;
SELECT TO_DATE('220818', 'YYMMDD') FROM DUAL;
  • RR : 1~50까지는 21세기로, 51~99까지는 20세기로 변환
SELECT TO_CHAR(TO_DATE('980630', 'YYMMDD'), 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('210818', 'YYMMDD'), 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('980630', 'RRMMDD'), 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('210818', 'RRMMDD'), 'YYYYMMDD') FROM DUAL;

TO_NUMBER

문자나 데이터를 숫자 데이터로 변환

SELECT TO_NUMBER('1,111', '9,999')+TO_NUMBER('2,222', '9,999') FROM DUAL;

5) NULL처리 함수

NVL

NULL값의 데이터를 대체값으로 변경

SELECT NAME, NVL(PANALTY, 0) FROM STUDENT;
SELECT NAME, NVL(DUAL_MAJOR, '단일전공') FROM STUDENT;

NVL2

NULL 여부에 따라 지정된 대체값을 출력

SELECT NAME WHERE NVL2(DUAL_MAJOR, '복수전공', '단일전공') FROM STUDENT;
SELECT NAME WHERE NVL2(DUAL_MAJOR, DUAL_MAJOR, '단일전공') FROM STUDENT;

NULLIF

특정한 값을 NULL로 변환

SELECT NULLIF(SCORE, 0) FROM STUDENT;

DECODE

특정 조건을 만족하는 값을 변환

SELECT DECODE(SCORE, 4, 'A', 3, 'B', 2, 'C') FROM STUDENT;
SELECT DECODE(SCORE, 4, 'A', 3, 'B', 2, 'C', 'F') FROM STUDENT;

CASE

특정 조건을 만족하는 값을 변환

SELECT
    CASE WHEN SCORE>=4 THEN '우수'
         WHEN SCORE>=2 THEN '보통'
         ELSE '미흡'
    END
FROM STUDENT;

📁 그룹 함수

하나의 결과값만을 반환하는 함수
반드시 GROUP BY 절과 함께 사용해야 한다.

SUM

컬럼의 합계 반환

SELECT SUM(SCORE) FROM STUDENT;

AVG

컬럼의 평균 반환

SELECT AVG(SCORE) FROM STUDENT;

MAX, MIN

컬럼의 행 중 최대값, 최소값 반환

SELECT MIN(NAME), MIN(SCORE), MIN(BIRTHDAY) FROM STUDENT

COUNT

NULL이 아닌 행의 갯수 반환

SELECT COUNT(*), COUNT(NAME) FROM STUDENT;
저작자표시

티스토리툴바