단일행 함수란 무엇인가

단일행 함수(single-row function) 는 입력으로 받은 값 한 개에 대해 결과값 한 개를 돌려주는 함수다. 100개 행이 있는 테이블에 단일행 함수를 적용하면, 함수는 각 행마다 독립적으로 한 번씩 실행되어 결과도 100개 나온다. 이는 여러 행을 하나로 요약하는 그룹 함수(group function) 와 정반대되는 개념이다. 그룹 함수는 15편에서 본격적으로 다루는데, 예를 들어 SUM(급여)는 100개 행의 급여를 모두 더해 결과를 딱 1개만 낸다. “행 하나에 값 하나(단일행 함수)“와 “여러 행을 값 하나로 요약(그룹 함수)“이라는 대비를 기억해 두면 이후 함수를 배울 때마다 어느 쪽인지 헷갈리지 않는다.

쉽게 말하면: 단일행 함수는 각 행에 붙어서 그 행의 값을 가공해 돌려주는 함수다. 100행이 들어가면 100개의 결과가 나온다.

이번 편에서는 문자 함수, 숫자 함수, 날짜 함수, 형변환 함수 네 가지 범주와 조건부 값을 만드는 CASE·DECODE를 다룬다. 모든 예제는 오라클(Oracle) SQL 문법 기준이며, MySQL과 다른 부분은 25편(DBMS별 문법 차이)에서 별도로 정리한다.

문자 함수

문자 함수는 문자열(글자들의 나열)을 자르고, 붙이고, 위치를 찾고, 길이를 재는 함수다.

문자열 인덱스는 1부터 시작한다

문자 함수를 배우기 전에 반드시 짚어야 할 규칙이 있다. 프로그래밍 언어(자바스크립트, 파이썬 등) 대부분은 문자열의 첫 글자 위치를 0으로 세는 0부터 시작하는 인덱스(zero-based index) 를 쓴다. 하지만 오라클 SQL의 문자 함수는 첫 글자를 1번으로 센다.

이 차이를 모르고 다른 언어의 습관대로 0을 넣으면 결과가 미묘하게 어긋난다. 예를 들어 SUBSTR('ABCD', 0, 2)는 얼핏 오류가 날 것 같지만, 오라클은 0을 1과 똑같이 취급해 첫 글자부터 시작한다. 즉 SUBSTR('ABCD', 0, 2)와 SUBSTR('ABCD', 1, 2)는 결과가 똑같이 AB다. 이는 SQLD 실행 결과 예측 문제에서 자주 쓰이는 함정이므로 반드시 기억해 두자.

자주 틀리는 점: “SQL 문자 함수의 인덱스는 1부터 시작한다”를 “0을 넣으면 오류가 난다”로 잘못 확장해 외우는 경우가 많다. 실제로는 0을 넣어도 오류 없이 1과 동일하게 처리된다.

SUBSTR: 문자열 일부 잘라내기

SUBSTR(문자열, 시작위치, 길이)는 문자열의 시작위치부터 길이만큼 잘라낸다. 길이를 생략하면 시작위치부터 끝까지 전부 가져온다.

시작위치가 음수면 문자열의 끝에서부터 센다. -1은 마지막 글자, -2는 마지막에서 두 번째 글자를 가리킨다. 반면 길이(세 번째 인자)가 음수이면 잘라낼 방향 자체가 존재하지 않아 결과가 빈 문자열이 된다(길이는 항상 시작위치에서 오른쪽으로 나아가는 글자 수이기 때문이다). 위 표의 마지막 두 행처럼 SUBSTR('ABCDEFGH', 7), SUBSTR('ABCDEFGH', -2), SUBSTR('ABCDEFGH', INSTR('ABCDEFGH', 'G'), 2)는 모두 결과가 GH로 같지만, SUBSTR('ABCDEFGH', 8, -2)만 빈 문자열이 되어 나머지 셋과 다르다는 것이 실제 SQLD 기출에 나온 실행 결과 예측 문제의 핵심이다.

그 외 자주 쓰는 문자 함수

자주 틀리는 점: LPAD와 RPAD를 헷갈려 방향을 반대로 외우는 경우가 많다. L은 Left(왼쪽), R은 Right(오른쪽)의 약자이며, 이는 채우는 문자를 어느 쪽에 붙이는지를 가리킨다. 자릿수를 맞춘 코드 생성(예: 사원번호 005처럼 앞을 0으로 채우기)에는 LPAD를 쓴다.

숫자 함수

숫자 함수는 반올림, 버림, 나머지 계산처럼 수치를 가공한다.

자주 틀리는 점: ROUND와 TRUNC를 같은 함수로 착각하는 경우가 많다. 두 함수 모두 “소수점 몇 번째 자리까지 남길지”를 두 번째 인자로 받는다는 점은 같지만, ROUND는 반올림하고 TRUNC는 그 아래 자리를 그냥 잘라 버린다(반올림 없음). ROUND(1234.567, 2)는 셋째 자리(7)를 반올림해 1234.57이 되지만, TRUNC(1234.567, 2)는 셋째 자리 이하를 그냥 버려 1234.56이 된다.

날짜 함수

날짜 함수는 오라클의 DATE 자료형(연·월·일·시·분·초를 통째로 담는 값)을 계산한다.

자주 틀리는 점 1: 날짜 연산 결과의 단위. 날짜 + 숫자에서 더해지는 숫자의 단위는 항상 일(day) 이다. 시간 단위(시·분)를 더하고 싶으면 숫자를 24(하루의 시간 수)나 1440(하루의 분 수)으로 나눠서 표현해야 한다(예: 1시간 뒤는 날짜 + 1/24). 반대로 날짜1 - 날짜2처럼 날짜끼리 빼면 결과는 그 사이의 일수를 나타내는 순수한 숫자가 된다. 이 둘의 방향(숫자를 더하면 날짜가 나오고, 날짜를 빼면 숫자가 나온다)을 혼동하는 문제가 자주 나온다.

자주 틀리는 점 2: TRUNC(날짜)는 시간만 지우지, 날짜를 지우지 않는다. TRUNC(SYSDATE)는 시·분·초를 00:00:00으로 만들 뿐, 연·월·일은 그대로 유지한다. “이번 달 전체”를 조건으로 걸 때 TRUNC(결제일시) = TO_DATE('2025-12', 'YYYY-MM')처럼 잘못 쓰면, TO_DATE가 일(日)이 빠진 값을 자동으로 그 달 1일로 보정하기 때문에 실제로는 12월 1일 하루치 데이터만 걸러진다. 이는 SQLD 기출에 실제로 나온 함정으로, 월 전체를 조건으로 걸려면 TO_CHAR(결제일시, 'YYYYMM') = '202512'처럼 문자열로 월을 비교하거나, 결제일시 >= TO_DATE('20251201','YYYYMMDD') AND 결제일시 < TO_DATE('20260101','YYYYMMDD')처럼 범위로 조건을 걸어야 한다.

변환 함수: TO_CHAR, TO_DATE, TO_NUMBER

오라클은 문자열, 숫자, 날짜라는 서로 다른 자료형(data type, 값의 종류를 구분하는 분류) 사이를 오갈 때 명시적인 변환 함수를 쓰도록 권장한다. 세 함수의 이름 구조는 모두 “TO_(변환하려는 목표 자료형)“으로 동일하다.

자주 틀리는 점: TO_DATE('2025-12', 'YYYY-MM')처럼 일(日) 정보를 생략하고 문자열을 날짜로 바꾸면, 오라클은 빠진 일 자리를 자동으로 1일로 채운다. 즉 결과는 2025-12-01 00:00:00이 된다. 이 보정 규칙을 모르면 “이번 달 전체”를 조회하려다 1일 하루치만 조회하는 실수를 하게 된다(위 날짜 함수 절의 함정과 같은 원리다).

또한 TO_NUMBER('ABC')처럼 숫자로 바꿀 수 없는 문자열을 변환하려 하면 오류(에러) 가 발생한다. TO_NUMBER는 문자열이 숫자로 해석 가능할 때만 성공하며, 문자가 섞인 진짜 텍스트는 변환할 수 없다.

CASE 식과 DECODE: 조건에 따라 다른 값 반환하기

지금까지의 함수는 입력값 하나를 정해진 규칙대로 가공했다. 하지만 “조건에 따라 다른 값을 보여주고 싶다”는 요구는 함수 하나로 해결되지 않는다. 이때 쓰는 것이 CASE 식과 DECODE 함수다. 둘 다 “여러 조건을 검사해 처음 맞는 조건의 결과를 반환한다”는 점에서 사실상 같은 역할을 한다.

쉽게 말하면: 프로그래밍 언어의 if-else if-else를 SQL 문장 안에서 쓰는 방법이다.

CASE 식: 두 가지 형태

-- 형태 1: 값 자체를 비교(단순 CASE)
SELECT 사원명,
       CASE 부서
           WHEN '영업' THEN '매출 부서'
           WHEN '홍보' THEN '매출 부서'
           ELSE '지원 부서'
       END AS 부서구분
FROM 사원;
 
-- 형태 2: 조건식을 직접 씀(검색 CASE, 범위·부등호 비교 가능)
SELECT 사원명,
       CASE
           WHEN 연봉 >= 5000 THEN '고액'
           WHEN 연봉 >= 3000 THEN '중간'
           ELSE '저액'
       END AS 연봉등급
FROM 사원;

자주 틀리는 점: 단순 CASE(형태 1)는 WHEN 뒤에 정확히 일치하는 값만 쓸 수 있어 WHEN NULL THEN ...처럼 써도 절대 일치하지 않는다(13편에서 다룬 대로 NULL은 =으로 비교할 수 없는 특수한 상태이기 때문에, CASE의 내부 비교 역시 이 규칙을 그대로 따른다). NULL을 조건으로 검사하려면 반드시 검색 CASE(형태 2)를 써서 WHEN 컬럼 IS NULL THEN ...처럼 표현해야 한다.

DECODE: 오라클 전용 조건 함수

DECODE(대상, 비교값1, 결과1, 비교값2, 결과2, ..., 기본값)은 단순 CASE와 정확히 같은 일을 하는 오라클 전용 함수다. 표준 SQL이 아니라 오라클에서만 동작하므로, MySQL로 옮길 때는 CASE로 바꿔야 한다(25편 참고).

자주 틀리는 점: 여기서 DECODE(보너스, NULL, -1, 0)은 CASE와 달리 NULL을 직접 비교값으로 써도 정상적으로 NULL을 감지한다. 이는 DECODE 함수 내부에서만 예외적으로 적용되는 동작으로, 일반적인 = 비교나 단순 CASE의 WHEN NULL과는 다르게 취급된다는 점이 SQLD에서 “칼럼 값이 NULL일 때 반환 결과가 다른 것을 고르라”는 형태로 자주 출제된다. 즉 CASE 칼럼 WHEN NULL THEN -1 ELSE 0 END(단순 CASE, NULL 절대 매칭 안 됨)와 DECODE(칼럼, NULL, -1, 0)(정상적으로 NULL 매칭됨)은 겉보기에 똑같아 보여도 결과가 다르다.

직접 해보기

직접 해보기 1 — SUBSTR에 0을 넣으면 어떻게 되는지 DUAL로 확인한다

SELECT SUBSTR('ABCDE', 1, 2) AS 시작1,
       SUBSTR('ABCDE', 0, 2) AS 시작0
FROM DUAL;

무엇을 보아야 하나: 시작1과 시작0 두 컬럼의 값이 같은지 확인하세요. 다른 프로그래밍 언어처럼 0을 넣으면 오류가 나거나 결과가 달라질 것이라고 예상했다면, 실제 결과와 비교해 보세요.

왜 이걸 해보나: “SUBSTR에 0을 넣으면 오류가 난다”고 잘못 외우는 경우가 많습니다. 실제로 0이 1과 똑같이 처리되는 것을 직접 확인해야 이 함정에 안 걸립니다.

직접 해보기 2 — TO_DATE에서 일(日)을 생략하면 무엇으로 채워지는지 확인한다

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS 오늘 FROM DUAL;
SELECT TO_DATE('2026-09', 'YYYY-MM') AS 변환결과 FROM DUAL;

무엇을 보아야 하나: 두 번째 결과에서 일(日) 자리가 몇 일로 채워져 있는지, 시·분·초는 몇 시로 나오는지 확인하세요.

왜 이걸 해보나: 일(日)이 자동으로 1일로 채워진다는 것을 모르면, “이번 달 전체”를 조회하려다 1일 하루치만 걸러내는 실수를 하게 됩니다.

직접 해보기 3 — DECODE·CASE·NVL 계열이 NULL을 각각 어떻게 처리하는지 비교한다

사원 표의 커미션 컬럼에는 NULL이 섞여 있습니다. 같은 컬럼에 여러 조건 함수를 나란히 적용해 결과를 비교합니다.

SELECT 사원명, 커미션,
       DECODE(커미션, NULL, -1, 커미션) AS d,
       CASE WHEN 커미션 IS NULL THEN -1 ELSE 커미션 END AS c,
       NVL(커미션, -1) AS nvl,
       NVL2(커미션, 1, -1) AS nvl2,
       COALESCE(커미션, -1) AS coalesce값
FROM 사원;

무엇을 보아야 하나: 커미션이 NULL인 행에서 d, c, nvl, coalesce값 네 컬럼의 값이 서로 같은지, nvl2만 다른 규칙으로 값을 내는지 확인하세요.

왜 이걸 해보나: DECODE와 단순 CASE의 WHEN NULL이 다르게 동작한다는 것, 그리고 NVL2는 NULL 여부에 따라 지정한 값을 바꿔치기하는 전혀 다른 함수라는 것을 결과로 직접 구분해 두면 헷갈리지 않습니다.

핵심 정리

• 오라클 문자 함수의 인덱스는 1부터 시작하며, 0은 1과 동일하게 처리된다.
• SUBSTR은 시작위치가 음수면 끝에서부터 세고, 길이가 음수면 빈 문자열을 반환한다.
• ROUND는 반올림하고 TRUNC는 그 아래 자리를 버린다(반올림 없음).
• 날짜에 숫자를 더하면 “일” 단위로 날짜가 이동하고, 날짜끼리 빼면 그 사이의 일수(숫자)가 나온다.
• TRUNC(날짜)는 시·분·초만 지우고, TO_DATE에서 일을 생략하면 자동으로 1일이 된다. 둘을 혼동하면 “이번 달 전체”를 잘못 조회하게 된다.
• 단순 CASE와 DECODE는 WHEN NULL로 NULL을 비교해도 매칭되지 않지만, DECODE는 예외적으로 NULL 비교값을 정상 인식한다.

마무리 복습

참고 자료

• Oracle Database SQL Language Reference - Single-Row Functions