[SQLD] 03. SQL 함수 (Function)

TJK·2025년 7월 23일

SQLD 자격증 공부 기록

목록 보기

3/60

SQL 함수 (Function)

SQL 함수는 데이터를 원하는 형태로 가공하거나 계산하여 새로운 값을 생성하는 역할을 함. 함수는 입력 값에 따라 하나의 결과를 반환하는 단일행 함수(Single-Row Function)와 여러 행을 그룹화하여 하나의 결과를 반환하는 다중행 함수(Multi-Row/Group Function)로 나뉨.

이번 장에서는 하나의 행(Row)을 입력받아 그 행에 대해 하나의 결과를 반환하는 단일행 함수를 중심으로 다룸. 단일행 함수는 SELECT, WHERE, ORDER BY 절 등에서 사용될 수 있음.

1. 문자 함수 (Character Functions)

문자열 데이터를 조작하고 가공하기 위한 함수.

함수	설명
`UPPER(char)`	문자열을 모두 대문자로 변환함.
`LOWER(char)`	문자열을 모두 소문자로 변환함.
`INITCAP(char)`	각 단어의 첫 글자만 대문자로, 나머지는 소문자로 변환함. (※ Oracle 전용)
`LENGTH(char)`	문자열의 길이를 숫자로 반환함.
`INSTR(char, substr)`	문자열 `char`에서 `substr`이 처음 나타나는 위치를 숫자로 반환함. (없으면 0)
`SUBSTR(char, pos, len)`	문자열 `char`의 `pos` 위치에서 `len` 길이만큼의 부분 문자열을 반환함.
`CONCAT(char1, char2)`	두 문자열을 합침. `
`LPAD(char, n, pad_str)`	`char`의 왼쪽에 `pad_str`을 채워 총 길이가 `n`이 되도록 함.
`RPAD(char, n, pad_str)`	`char`의 오른쪽에 `pad_str`을 채워 총 길이가 `n`이 되도록 함.
`REPLACE(char, old, new)`	`char`에서 모든 `old` 문자열을 `new` 문자열로 교체함.
`TRIM(char)`	`char`의 양쪽 공백을 제거함. (`LTRIM`, `RTRIM`은 각각 왼쪽, 오른쪽 공백 제거)

-- 문자 함수 활용 예시
SELECT
    name,
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name,
    LENGTH(name) AS name_length,
    SUBSTR(name, 1, 2) AS name_substring,
    /* user_id를 5자리로 맞추기 위해 왼쪽에 '0'을 채움 */
    LPAD(user_id, 5, '0') AS padded_user_id
FROM
    User
WHERE 
    user_id = 1;

-- content에서 '#일상'을 '#데일리'로 교체
SELECT
    content AS original_content,
    REPLACE(content, '#일상', '#데일리') AS replaced_content
FROM
    Post
WHERE
    content LIKE '%#일상%';

2. 숫자 함수 (Numeric Functions)

숫자 데이터를 처리하기 위한 함수. 주로 산술 연산에 사용됨.

함수	설명
`ROUND(n, [i])`	숫자 `n`을 소수점 `i`+1 자리에서 반올림함. `i` 생략 시 정수로 반올림.
`TRUNC(n, [i])`	숫자 `n`을 소수점 `i` 자리에서 버림(절사)함. `i` 생략 시 정수로 버림.
`MOD(n1, n2)`	`n1`을 `n2`로 나눈 나머지를 반환함.
`CEIL(n)`	`n`보다 크거나 같은 정수 중 가장 작은 값을 반환함. (올림)
`FLOOR(n)`	`n`보다 작거나 같은 정수 중 가장 큰 값을 반환함. (내림)

-- 숫자 함수 활용 예시
-- Oracle의 DUAL 테이블은 함수 결과 확인 등 간단한 계산을 위해 사용하는 시스템 제공 테이블임.
SELECT
    ROUND(3.141592, 2) AS round_val, -- 결과: 3.14
    TRUNC(3.141592, 2) AS trunc_val, -- 결과: 3.14
    CEIL(3.14) AS ceil_val,         -- 결과: 4
    FLOOR(3.14) AS floor_val,       -- 결과: 3
    MOD(10, 3) AS mod_val           -- 결과: 1
FROM
    DUAL; -- ※ Oracle에서 사용하는 가상 테이블

3. 날짜 함수 (Date Functions)

날짜(Date) 타입 데이터를 처리하기 위한 함수.

함수	설명
`SYSDATE`	현재 시스템의 날짜와 시간을 반환함. (함수지만 괄호 없음)
`ADD_MONTHS(d, n)`	날짜 `d`에 `n`개월을 더한 날짜를 반환함.
`MONTHS_BETWEEN(d1, d2)`	날짜 `d1`과 `d2` 사이의 개월 수 차이를 반환함.
`LAST_DAY(d)`	날짜 `d`가 속한 달의 마지막 날짜를 반환함.
`NEXT_DAY(d, char)`	날짜 `d` 이후에 처음으로 오는 `char` 요일의 날짜를 반환함.
`ROUND(d, [fmt])`	날짜 `d`를 형식 `fmt` 기준으로 반올림함. (예: `MM`은 16일을 기준으로 반올림)
`TRUNC(d, [fmt])`	날짜 `d`를 형식 `fmt` 기준으로 버림함. (예: `MM`은 해당 월의 1일로 버림)

-- 날짜 함수 및 연산 예시 (2024-07-15 기준)
SELECT
    SYSDATE AS current_datetime,
    SYSDATE + 1 AS one_day_later,   -- 1일 후 (날짜 연산)
    SYSDATE - 1/24 AS one_hour_ago, -- 1시간 전 (날짜 연산)
    ADD_MONTHS(SYSDATE, 3) AS three_months_later,
    LAST_DAY(SYSDATE) AS last_day_of_month
FROM
    DUAL;

4. 변환 함수 (Conversion Functions)

데이터 타입을 명시적으로 변환하기 위한 함수.

함수	설명
`TO_CHAR(d/n, [fmt])`	날짜/숫자를 형식 `fmt`에 맞는 문자열로 변환함.
`TO_DATE(char, [fmt])`	형식 `fmt`에 맞는 문자열 `char`를 날짜 타입으로 변환함.
`TO_NUMBER(char)`	문자열 `char`를 숫자 타입으로 변환함. (숫자 형태의 문자열만 가능)

-- 변환 함수 예시
SELECT
    name,
    -- 날짜를 'YYYY-MM-DD (요일)' 형식의 문자로 변환
    TO_CHAR(registration_date, 'YYYY-MM-DD (DY)') AS reg_date_char
FROM
    User
WHERE
    -- 문자열 '20230101'을 날짜 타입으로 변환하여 비교
    registration_date > TO_DATE('20230101', 'YYYYMMDD');

5. NULL 처리 함수

NULL 값을 다른 의미 있는 값으로 대체하거나 처리하기 위한 함수.

함수	설명
`NVL(expr1, expr2)`	`expr1`이 `NULL`이면 `expr2`를, 아니면 `expr1`을 반환함.
`NVL2(expr1, expr2, expr3)`	`expr1`이 `NULL`이 아니면 `expr2`를, `NULL`이면 `expr3`을 반환함.
`COALESCE(expr1, expr2, ...)`	표현식 목록에서 `NULL`이 아닌 첫 번째 표현식을 반환함.
`NULLIF(expr1, expr2)`	`expr1`과 `expr2`가 같으면 `NULL`을, 다르면 `expr1`을 반환함.

-- NULL 처리 함수 예시
SELECT
    bio,
    NVL(bio, '자기소개 없음') AS nvl_bio,
    NVL2(manager_id, '관리자 있음', '관리자 없음') AS nvl2_manager,
    COALESCE(phone_number, email, '연락처 없음') AS contact_info
FROM
    User;

6. 조건부 표현식 (CASE Expression)

IF-THEN-ELSE 논리를 SQL로 구현하는 표현식. SELECT, WHERE, ORDER BY 절에서 모두 사용 가능.

Simple CASE: 하나의 표현식을 여러 값과 비교.
Searched CASE: 여러 독립적인 조건을 평가.

-- CASE 표현식 예시
SELECT
    name,
    /* Searched CASE: 가입 연도에 따라 등급 부여 */
    CASE
        WHEN TO_CHAR(registration_date, 'YYYY') >= '2023' THEN '새싹 회원'
        WHEN TO_CHAR(registration_date, 'YYYY') >= '2021' THEN '일반 회원'
        ELSE '장기 회원'
    END AS user_grade,
    
    /* Simple CASE: post_type 값에 따라 한글 이름 부여 */
    CASE post_type
        WHEN 'photo' THEN '사진'
        WHEN 'video' THEN '영상'
        ELSE '기타'
    END AS post_type_kor
FROM
    User u, Post p
WHERE
    u.user_id = p.user_id;

7. 시험 문제 유형 및 함정 포인트

NULL의 전파성: NULL이 포함된 모든 산술 연산(+, -, *, /)과 문자열 연결(||)의 결과는 NULL이 됨. (예: 100 + NULL은 NULL).
SUBSTR과 INSTR 혼동: 두 함수의 파라미터와 반환값이 다르므로 명확히 구분해야 함. SUBSTR은 문자열을 반환, INSTR은 위치(숫자)를 반환.
날짜 ROUND와 TRUNC: 날짜 ROUND는 'MM' 기준 16일을 기점으로 다음 달/이번 달로 결정되는 반면, TRUNC는 무조건 버림(해당 월 1일) 처리됨. 이 차이를 묻는 문제가 자주 출제됨.
NULLIF의 용도: 두 값이 같으면 NULL을 반환하는 특성을 이용해, SUM(A) / NULLIF(SUM(B), 0)과 같이 '0으로 나누기(division by zero)' 오류를 방지하는 용도로 사용될 수 있음을 이해해야 함.
TO_CHAR 날짜 형식: MM(월), MI(분) / DY(요일 약어), DAY(요일 전체) 등 다양한 날짜 포맷팅 기호를 정확히 암기해야 함.

8. DBMS별 문법 차이

INITCAP: Oracle 고유 함수. MySQL/SQL Server에서는 UCASE, LCASE, SUBSTRING 등을 조합하여 구현해야 함.
NVL: Oracle 함수. SQL Server에서는 ISNULL(), MySQL에서는 IFNULL()이 동일한 기능을 함. ANSI SQL 표준은 COALESCE임.
DUAL 테이블: Oracle에서 사용하는 가상 테이블. MySQL이나 SQL Server에서는 FROM 절 없이 SELECT 1+1; 같은 구문 실행이 가능함.
날짜 함수: ADD_MONTHS, MONTHS_BETWEEN 등은 Oracle 고유 함수. MySQL은 DATE_ADD(), TIMESTAMPDIFF(), SQL Server는 DATEADD(), DATEDIFF() 등 유사한 기능을 하는 다른 이름의 함수를 사용함. SQLD는 Oracle 기반으로 출제되는 경향이 있음.