문자·숫자·날짜 함수

핵심 요약

단일행 함수가 입력 행마다 결과 한 건을 만드는 원리와 자주 나오는 함수 조합을 익힌다.

핵심 질문

1. 문자·숫자·날짜 함수에서 반드시 구분해야 할 개념과 결과 규칙은 무엇인가?
2. 0건·1건·여러 건과 NULL·동점·중복 데이터에서 결과가 어떻게 달라지는가?
3. 비슷해 보이는 문법과 결과가 같아지는 조건, 달라지는 조건은 무엇인가?
4. 작은 샘플 데이터를 이용해 결과를 실수 없이 예측하는 순서는 무엇인가?

학습 목표

• 문자·숫자·날짜 함수의 입력과 결과를 예측한다.
• 날짜 계산에서 SYSDATE, ADD_MONTHS, MONTHS_BETWEEN을 구분한다.

개념 지도

입력 값과 타입 확인 → 함수 적용 → NULL·NLS·형변환 확인 → 결과 타입 검증

핵심 내용

단일행 함수는 입력 행마다 결과를 하나씩 반환하며 중첩할 수 있다.

• 문자: UPPER, LOWER, SUBSTR, INSTR, LENGTH, TRIM, REPLACE
• 숫자: ROUND, TRUNC, MOD, CEIL, FLOOR
• 날짜: SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY

SELECT SUBSTR('SQLP-2026', 1, 4),
       ROUND(123.456, 2),
       ADD_MONTHS(DATE '2026-01-31', 1)
FROM dual;

Oracle에서 문자열 위치는 기본적으로 1부터 센다. ROUND(date, 'MM')과 TRUNC(date, 'MM')도 숫자 함수와 비슷한 이름이지만 날짜 경계를 다룬다. 월말 날짜에 월을 더할 때는 달마다 날짜 수가 다르므로 ADD_MONTHS의 규칙을 확인한다.

흔한 오해와 주의점

• ROUND와 TRUNC는 반올림과 버림의 차이다.
• LENGTH는 문자 길이, LENGTHB는 바이트 길이로 다국어 문자열에서 다를 수 있다.
• 함수의 입력 타입과 반환 타입을 확인하고 NULL 입력의 결과도 함께 점검한다.

문항 풀이 보강: 함수 결과를 손으로 계산하기

단일행 함수는 입력 행마다 한 결과를 반환하므로 1:M 조인의 M쪽 행에도 사용할 수 있다. 다중행 함수는 여러 입력 행을 한 값으로 요약한다. 두 종류 모두 “최종 반환값 하나”일 수 있으므로 반환 개수만으로 분류하지 않는다.

문자 함수

SUBSTR('SQL STUDY', 1, 3)  -- SQL
INSTR('SQL STUDY', 'S')  -- 첫 S의 위치
TRIM(CHR(10) FROM text_col)  -- 지정 문자를 양 끝에서 제거

TRIM은 문자열 중간의 줄바꿈까지 제거하지 않는다. REPLACE는 문자열 전체에서 일치 부분을 바꾼다. LENGTH는 문자 수, LENGTHB는 바이트 수다.

숫자 함수

날짜 함수

ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN의 입력과 반환 타입을 확인한다. 날짜에 1/24를 더하면 한 시간, 1/24/60을 더하면 일 분이다.

문제에 적용하는 순서

안쪽 함수부터 계산하고 각 중간값의 타입을 적는다. 줄바꿈은 CHR(10), 날짜는 일수 단위라는 점을 먼저 표시한 뒤 바깥 함수를 계산한다.

함수군별 대표 예

SELECT UPPER('Kim'),
       SUBSTR('ORACLE', 2, 3),
       INSTR('BANANA', 'NA')
FROM dual;

SELECT ROUND(123.456, 2),
       TRUNC(123.456, 2),
       MOD(10, 3)
FROM dual;

SELECT TRUNC(SYSDATE, 'MM') AS month_start,
       ADD_MONTHS(DATE '2026-01-31', 1) AS next_month,
       MONTHS_BETWEEN(DATE '2026-03-31', DATE '2026-01-31')
FROM dual;

ROUND와 TRUNC는 숫자와 날짜에 모두 사용되지만 결과 의미가 다르다. ADD_MONTHS는 월말 규칙을 가지므로 일수 더하기와 같지 않다.

함수 적용 위치와 결과

-- Column을 가공
WHERE TRUNC(order_date) = DATE '2026-07-27'

-- 원본 범위 사용
WHERE order_date >= DATE '2026-07-27'
  AND order_date <  DATE '2026-07-28'

두 SQL은 날짜에 시간 값이 포함될 수 있는지와 NULL 처리 방식이 같을 때에만 같은 결과를 만든다. 함수 적용 전후의 경계값을 예제로 확인한다.

NLS와 타입

TO_CHAR·TO_DATE는 Format Model을 명시하고, 월 이름·소수점·통화 기호처럼 NLS 의존 결과를 외부 Interface Key로 사용하지 않는다. 함수의 반환 타입이 다음 비교·정렬에서 어떤 변환을 유발하는지도 확인한다.

결과를 검증하는 순서

1. 각 Query Block이 만드는 한 행의 의미를 먼저 적습니다.
2. 조건을 적용하기 전 원본 행과 적용 후 남는 행을 작은 표로 그립니다.
3. NULL 비교가 TRUE, FALSE, UNKNOWN 중 무엇인지 구분합니다.
4. 중복 제거, 그룹화, 정렬과 행 제한이 적용되는 순서를 확인합니다.
5. 데이터가 0건·1건·여러 건일 때도 같은 규칙이 성립하는지 검증합니다.

실무와 시험에서 함께 확인할 항목

• ORDER BY가 없다면 결과 순서를 가정하지 않습니다.
• 문자열·숫자·날짜 비교에서는 데이터 타입과 명시적 형변환을 확인합니다.
• 같은 결과처럼 보이는 SQL도 NULL과 중복이 있을 때 달라질 수 있습니다.
• 문법을 외우기 전에 샘플 데이터 3~5행으로 결과를 직접 계산합니다.

마지막 점검

• 작성 순서가 아니라 SQL의 논리적 처리 순서로 결과를 계산합니다.
• NULL을 0이나 빈 값과 같은 것으로 취급하지 않습니다.
• ORDER BY가 없는 결과 순서와 DISTINCT 없는 중복 제거를 가정하지 않습니다.
• 비슷한 문법은 0건·다건·NULL 데이터를 넣어 결과가 정말 같은지 확인합니다.

복습 문제

1. 문자열에서 부분 문자열의 위치를 찾는 함수는?
2. 월의 마지막 날짜를 구하는 함수는?
3. 샘플 데이터 3행으로 결과를 직접 계산할 수 있는가?
4. NULL이 포함될 때 결과가 달라지는 지점은 어디인가?