Introduction

전 포스팅에서는 SELECT, FROM, WHERE, ORDER BY를 활용하여 간단하게 조회를 해보았습니다. 이번에는 기타 잡다한 함수들에 대해서 알아보도록 하겠습니다.

함수(FUNCTION)

다른 언어에도 함수라는 개념이 있습니다. SQL도 비슷합니다.

SQL에서 함수란 칼럼값을 읽어들여 함수를 실행한 결과를 반환하는 것입니다. 다른 언어에서의 함수랑 비슷한 개념인 것을 알 수 있습니다. 다만, 다른 언어와 달리 함수의 종류가 크게 두가지로 나뉩니다. 바로 단일행 함수와 그룹 함수입니다.

• 단일행 함수 : N개의 값을 읽어들여서 N개의 결과값을 반환(매행마다 함수실행결과를 반환)
• 그룹 함수 : N개의 값을 읽어들여서 1개의 결과값을 반환(그룹을 지어 그룹별로 함수 실행결과 반환)

즉 들어가는 건 같아도 나오는게 다릅니다. 이 둘을 혼용해서 쓸 때 조심해야 됩니다. 행의 갯수가 다르기 때문입니다.

단일행 함수

N개의 값을 읽어들여서 N개의 결과값을 반환하는 함수입니다.

문자 처리 함수

LENGTH / LENGTHB

• LENGTH(컬럼 | '문자열') : 해당 문자열의 글자수를 반환
• LENGTHB(컬럼 | '문자열') : 해당 문자열의 바이트수를 반환

한글은 한 글자에 3BYTE, 영어는 한 글자에 1BYTE를 차지합니다.

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

SELECT LENGTH('ORACLE'), LENGTHB('ORACLE')
FROM DUAL;  -- 6	6

여기서 DUAL이라는 낯선 테이블이 보입니다. DUAL은 오라클에서 제공하는 테이블로 관리자인 SYS 사용자가 관리하는 통장이지만, 어느 사용자든 접근이 가능한 테이블입니다. 주요 역할은 결과값을 확인 할 때 사용하는 여분의 테이블이라 생각하시면 됩니다.

INSTR

문자열로부터 특정 문자의 시작 위치를 찾아서 NUMBER로 반환합니다.

INSTR(컬럼 | '문자열', '찾고자하는 문자', ['찾을 위치의 시작값', 순번])

바로 예시를 보도록 하겠습니다.

SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL; -- 앞쪽에 있는 첫 B는 3번째 위치
-- 찾을 위치 시작값 : 1 , 순번 : 1 => 기본값
SELECT INSTR('AABAACAABBAA', 'B', 1) FROM DUAL;
SELECT INSTR('AABAACAABBAA', 'B', -1) FROM DUAL; -- 뒤에서부터 찾는다. 결과 : 10
SELECT INSTR('AABAACAABBAA', 'B', 1, 2) FROM DUAL; -- 순번을 제시하려면 찾을 위치의 시작값을 표시, 결과 : 9
SELECT INSTR('AABAACAABBAA', 'B', 1, 3) FROM DUAL; -- 결과 : 10

몇 가지 주목해야 될 사항들이 있습니다.

• 문자열의 인덱스는 1부터 시작합니다. 0이 아닙니다.
• 인덱스가 -1일 경우 맨 뒤입니다. -2는 맨 뒤에서 두 번째, ... 등 음의 인덱스도 있습니다.

SUBSTR

자주쓰이는 함수로 문자열에서 특정 문자열을 추출해서 반환합니다.

표현법은 아래와 같습니다.

SUBSTR(STRING, POSITION, [LENGTH])

• STRING : 문자타입의 컬럼 또는 '문자열'
• POSITION : 문자열 추출할 시작위치의 값
• LENGTH : 추출할 문자 갯수(생략하면 끝까지)

바로 예시를 보도록 하겠습니다.

SELECT SUBSTR('SHOWMETHEMONEY', 7) FROM DUAL; -- THEMONEY
SELECT SUBSTR('SHOWMETHEMONEY', 5, 2) FROM DUAL; -- ME
SELECT SUBSTR('SHOWMETHEMONEY', 1, 6) FROM DUAL; -- SHOWME
SELECT SUBSTR('SHOWMETHEMONEY', -8, 3) FROM DUAL; -- THE

LPAD / RPAD

문자열에 덧붙이고자 하는 문자를 왼쪽(LPAD) 또는 오른쪽(RPAD)에 붙여서 최종 N길이만큼의 문자열을 반환합니다. 표현법은 아래와 같습니다.

LPAD/RPAD(STRING, 최종적으로 반환할 문자의 길이, [덧붙이고자하는 문자])

기본적으로 덧붙이고자하는 문자를 적어주지 않을 경우 공백으로 채웁니다. 만약 사원들의 주민등록번호를 900101-1** 과 같은 형식으로 적어야 될 때 다음과 같이 적을 수 있습니다.

SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*')
FROM EMPLOYEE;

아니면 연결 연산자 ||를 사용하는 방법도 있다.

SELECT EMP_NAME, SUBSTR(EMP_NO, 1, 8) || '*******'
FROM EMPLOYEE;

LTRIM / RTRIM

문자열에서 특정 문자를 제거한 나머지를 반환합니다.

LTRIM/RTRIM(STRING, [제거하고자하는 문자들])

기본적으로 제거하고자 하는 문자를 적어주지 않을 경우 공백으로 설정합니다.

바로 예시를 살펴보도록 하겠습니다.

SELECT LTRIM('    AB  C') FROM DUAL; 
-- 앞에서부터 다른문자가 나올때 까지만 공백제거, 결과 : AB  C
SELECT LTRIM('123123AB123', '123') FROM DUAL;
-- 결과 : AB123
SELECT LTRIM('ACABACCDD', 'ABC') FROM DUAL; 
-- 제거하고자하는 문자는 문자열이아닌 문자 각자 하나하나의 문자들, 결과 : DD
SELECT RTRIM('574185ABC123', '0123456789') FROM DUAL;
-- 결과 : 574185ABC

TRIM

문자열의 앞/뒤/양쪽에 있는 지정한 문자들을 제거한 나머지 문자열 반환합니다.

TRIM([LEADING | TRAILING | BOTH] 제거하고자하는 문자열 FROM 문자열)

기본적으로 제거하고자 하는 문자를 적어주지 않을 경우 공백으로 설정합니다.
앞쪽 설정은 다음과 같습니다.

• LEADING == LTRIM
• TRAILING == RTRIM
• BOTH == 양쪽 : DEFAULT

그런데... 어차피 헷갈리니 보통 이 기능들은 잘 안씁니다. 그냥 이렇게 기억해두면 편합니다.

• LTRIM : 왼쪽 제거
• RTRIM : 오른쪽 제거
• TRIM : 양쪽 제거

바로 예시를 살펴보도록 하겠습니다.

SELECT TRIM('Z' FROM 'ZZZZZAZBZCZZZZZ') FROM DUAL; -- AZBZC

LOWER / UPPER / INITCAP

• LOWER : 다 소문자로 변경한 문자열 반환
• UPPER : 다 대문자로 변경한 문자열 반환
• INITCAP : 띄어쓰기 기준 첫 글자마다 대문자로 변경한 문자열 반환

SELECT LOWER('Welcome To My World!') FROM DUAL;
-- welcome to my world!
SELECT UPPER('Welcome To My World!') FROM DUAL;
-- WELCOME TO MY WORLD!
SELECT INITCAP('welcome to my world!') FROM DUAL;
-- Welcome To My World!

CONCAT

문자열 두개 전달받아 하나로 합친 후 반환합니다.

CONCAT(STRING1, STRING2)

바로 예시를 보도록 하겠습니다.

SELECT CONCAT('가나다', 'ABC') FROM DUAL; -- 두개의 문자열만 가능
SELECT '가나다' || 'ABC' FROM DUAL;

REPLACE

특정문자열에서 특정부분을 다른부분으로 교체

REPLACE(문자열, 찾을 문자열, 변경할문자열)

바로 예시를 보도록 하겠습니다.

SELECT EMAIL, REPLACE(EMAIL, 'naver.com', 'gmail.com')
FROM EMPLOYEE;

숫자 처리 함수

ABS

숫자의 절대값을 구해주는 함수입니다.

SELECT ABS(-10), ABS(-6.3) FROM DUAL; 
-- 10		6.3

MOD

두 수를 나눈 나머지값을 반환합니다.

MOD(NUMBER, NUMBER)

SELECT MOD(10, 3) FROM DUAL;
-- 1
SELECT MOD(10.9, 3) FROM DUAL;
-- 1.9

ROUND

반올림한 결과를 반환합니다.

ROUND(NUMBER, [위치])

SELECT ROUND(123.456, 0) FROM DUAL; -- 기본자리수는 소수점 첫번째 자리에서 반올림 : 0
-- 123
SELECT ROUND(123.456, 1) FROM DUAL; -- 양수로 증가할 수록 소수점 뒤로 한칸씩 이동
-- 123.5
SELECT ROUND(123.456, -1) FROM DUAL; -- 음수로 감소할 수록 소수점 앞자리로 이동
-- 120

CEIL / FLOOR

각각 올림과 내림한 결과를 반환합니다. 자릿수는 0으로 고정입니다.

CEIL(NUMBER)
FLOOR(NUMBER)

SELECT CEIL(123.456) FROM DUAL;
-- 124
SELECT FLOOR(123.955) FROM DUAL;
-- 123

TRUNC

버림을 처리합니다. 위치를 지정 가능합니다.

TRUNC(NUMBER, [위치])

SELECT TRUNC(123.952) FROM DUAL;
-- 123
SELECT TRUNC(123.952, 1) FROM DUAL;
-- 123.9
SELECT TRUNC(123.952, -1) FROM DUAL;
-- 120

날짜 처리 함수

기본적으로 NUMBER 와 같은 숫자 타입, CHAR, VARCHAR2 같은 문자열 타입처럼 날짜를 나타내는 DATE 타입이 존재합니다. 이를 다루기 위해 필요한 함수들을 소개합니다.

SYSDATE

시스템의 현재 날짜 및 시간을 반환합니다.

SELECT SYSDATE FROM DUAL;

MONTHS_BETWEEN

두 날짜 사이의 개월 수를 계산할 때 사용합니다. 기본적으로 일, 시, 분, 초로 인해 소수점으로 나오게 되는데, 정수 형태로 "N개월 차"임을 언급하고 싶을 경우 올림(CEIL 등)을 해주면 됩니다.

마찬가지로 일수 계산에서도 일수 미만 단위로 인해 소수점이 나오는데, 경우에 따라 올림과 버림을 사용해서 적절하게 처리하는 것이 좋습니다.

이를 이용해서 사원들의 사원명, 입사일, 근무일 수, 근무개월 수를 조회하는 쿼리는 아래와 같습니다.

SELECT EMP_NAME, HIRE_DATE, FLOOR(SYSDATE - HIRE_DATE),
    CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월차' AS "근속개월"
FROM EMPLOYEE;

ADD_MONTHS

특정 날짜에 NUMBER개월 수를 더해서 반환합니다.

SELECT ADD_MONTHS(SYSDATE, 4) FROM DUAL;

NEXT_DAY

특정 날짜 이후 가장 가까운 요일의 날짜를 반환합니다.

NEXT_DAY(DATE, 요일(문자 | 숫자))

SELECT NEXT_DAY(SYSDATE, '토요일') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, '토') FROM DUAL;
-- 1: 일, 2: 월 ... 7: 토
SELECT NEXT_DAY(SYSDATE, 7) FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL; -- 언어 설정 때문에 에러

국가 언어 설정으로 인해서 한글로 입력하면 작동이 잘되고, 오히려 영어로 입력하면 중간에 에러가 뜨게 됩니다. 만약 국가를 미국으로 바꾸고 싶다면 다음 쿼리를 입력해주면 됩니다.

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;

LAST_DAY

해당 달의 마지막 날짜를 구합니다. 2024년 2월을 기준으로 24/02/29가 마지막 날짜가 됩니다.

SELECT LAST_DAY(SYSDATE) FROM DUAL;

EXTRACT

특정 날짜로부터 연 | 월 | 일 값을 추출해서 NUMBER를 반환하는 함수입니다. 각각 아래와 같은 표현식이 있습니다.

EXTRACT(YEAR FROM DATE) : 연도만 추출
EXTRACT(MONTH FROM DATE) : 월만 추출
EXTRACT(DAY FROM DATE) : 일만 추출

이를 활용해서 사원의 사원명, 입사년도, 입사월, 입사일을 조회하면 아래와 같습니다.

SELECT EMP_NAME,
    EXTRACT(YEAR FROM HIRE_DATE) AS "입사년도",
    EXTRACT(MONTH FROM HIRE_DATE) AS "입사월",
    EXTRACT(DAY FROM HIRE_DATE) AS "입사일"
FROM EMPLOYEE
ORDER BY 2, 3, 4;

... 함수가 상당히 많습니다. 남은 단일행 함수들과 그룹 함수들은 다음 포스팅에 정리하겠습니다.