(DB실무) Part4-SQL 기본 및 활용-3.Function
2026년 11월 8일 공유
SQL 단일행 함수와 정규 표현식 함수
개요
오라클 SQL의 함수 중 단일행 함수를 중심으로 종류와 사용법을 살펴본 강의다. 문자열·숫자·날짜 처리, 데이터 형 변환, 조건 분기, NULL 처리와 정규 표현식 함수의 기초를 다룬다.
핵심 개념
- 함수는 특정 기능을 수행하는 연산 단위다. SQL에서는 오라클이 제공하는 내장 함수와 사용자가 정의하는 함수가 있으며, 일반적인 함수 학습은 내장 함수를 대상으로 한다.
- 단일행 함수는 각 행에 개별 적용되어 행마다 결과를 반환한다. 한 행의 하나 또는 여러 값을 사용할 수 있지만, 여러 행을 묶어 계산하지는 않는다.
- 여러 행을 대상으로 하는 함수는 다중행 함수이며, 그룹 함수와 윈도우 함수 등이 이에 해당한다.
- 단일행 함수는 주로
SELECT에서 사용되지만WHERE,ORDER BY에서도 사용할 수 있고, 함수 안에 다른 함수를 넣는 중첩도 가능하다.
- 함수는 데이터 형식과 역할에 따라 문자형·숫자형·날짜형·변환형·제어형·NULL 관련 함수로 나뉜다.
상세 정리
1. 문자형 함수
문자열을 변환하거나 결합하고, 일부를 추출하거나 길이를 확인한다.
LOWER,UPPER: 문자열을 모두 소문자 또는 대문자로 변환한다. 대소문자 차이로 값이 다르게 취급되는 비교에서 유용하다.
ASCII,CHR: 문자에 해당하는 ASCII 코드를 반환하거나, 코드를 문자로 변환한다. 예를 들어 대문자A의 ASCII 코드는 65다.
CONCAT: 문자열을 결합한다. 문자열 연결 연산자||로도 같은 작업을 할 수 있다.
SUBSTR: 문자열에서 지정한 위치부터 일부를 추출한다. 길이를 생략하면 끝까지, 길이를 지정하면 해당 길이만큼 반환한다.
LENGTH: 문자열의 길이를 반환한다.
LTRIM,RTRIM,TRIM: 왼쪽·오른쪽·양쪽의 지정 문자를 제거한다. 제거 문자를 생략하면 공백을 제거한다. 특히 비교 전에 앞뒤 공백을 제거할 때 유용하다.
- 함수 중첩을 이용하면 문자열의 길이를 구하거나, 이름의 마지막 글자를 기호로 바꾸는 등 여러 작업을 조합할 수 있다.
2. 정규 표현식 함수
정규 표현식은 문자열에서 특정 패턴을 찾거나 검증·치환하기 위한 문자와 메타문자의 조합이다. 강의에서는 시험 범위에 새로 추가된 내용으로 소개했다.
대표 함수는 다음과 같다.
REGEXP_LIKE: 정규 표현식 패턴과 일치하는 행을 찾는다. 주로WHERE조건에서 사용한다.
REGEXP_REPLACE: 패턴과 일치하는 문자열을 다른 문자열로 바꾼다.
REGEXP_INSTR: 패턴이 처음 나타나는 위치를 반환한다.
REGEXP_SUBSTR: 패턴과 일치하는 문자열을 반환한다.
REGEXP_COUNT: 패턴과 일치하는 부분의 개수를 반환한다.
주요 메타문자:
.: 임의의 한 문자
^,$: 문자열의 시작과 끝
*,+,?: 앞 문자의 반복 횟수를 각각 0회 이상, 1회 이상, 0회 또는 1회로 지정
|: 대안 중 하나와 일치
()와{}: 패턴을 묶거나 반복 횟수를 지정
[],[^...],[a-z]: 문자 집합, 제외 집합, 범위 지정
\d,\w,\s: 숫자, 문자·숫자·밑줄, 공백 문자 등을 나타내는 패턴
예를 들어 주소에서 서울 또는 인천이 포함된 행을 찾고, 주소 안의 숫자를
X로 바꾸려면 REGEXP_LIKE와 REGEXP_REPLACE를 함께 사용할 수 있다.3. 숫자형 함수
숫자 계산과 반올림·절삭 등에 사용한다.
ABS: 절댓값
SIGN: 양수는 1, 음수는 -1, 0은 0 반환
MOD: 나눗셈의 나머지 반환. 값을 여러 그룹으로 나누는 데 활용할 수 있다.
CEIL,FLOOR: 각각 입력값 이상인 가장 작은 정수, 입력값 이하인 가장 큰 정수를 반환한다. 음수에서는 수직선 기준으로 결과를 확인해야 한다.
ROUND: 지정 자릿수까지 반올림한다. 자릿수를 생략하면 정수로 반올림한다.
TRUNC: 지정 자릿수 아래를 절삭한다. 자릿수를 생략하면 소수부를 제거한다.
- 이외에도
POWER,SQRT, 로그 및 삼각 함수 등이 있다.
4. 변환형 함수와 데이터 형식
명시적 변환은 함수를 사용해 변환 형식을 분명히 지정하는 방식이다.
TO_CHAR: 숫자나 날짜를 문자열로 변환한다. 날짜 형식을 지정할 수도 있다.
TO_NUMBER: 문자열을 숫자로 변환한다.
TO_DATE: 문자열을 날짜로 변환한다. 필요하면 입력 형식을 지정한다.
함수를 쓰지 않아도 데이터베이스가 자동으로 변환하는 경우가 있는데, 이를 암시적 변환이라고 한다. 강의에서는 예상하지 못한 오류나 성능 문제를 줄이기 위해 암시적 변환에 의존하지 말고 명시적 변환을 사용하는 편이 좋다고 강조한다.
5. 날짜형 함수
SYSDATE: 현재 날짜와 시간을 제공하는 시스템 값이다.DUAL은 값을 조회할 때 사용하는 더미 테이블이며, 날짜 정보가 저장된 테이블은 아니다.
EXTRACT: 날짜 값에서 연도(YEAR), 월(MONTH), 일(DAY) 등을 추출한다. 날짜 데이터에 적용하며, 날짜처럼 보이는 문자열은 먼저TO_DATE로 변환해야 한다.
TRUNC: 날짜의 시간 부분 등을 절삭할 때 사용한다. 날짜 차이를 계산할 때 시·분·초의 영향을 제거하는 데 활용할 수 있다.
- 날짜를 표시 형식에 맞춘 문자열로 만들려면
TO_CHAR를 사용한다.
6. 제어 함수: CASE와 DECODE
CASE는 SQL 안에서 조건에 따라 결과를 선택하는 표현식이다.
- 조건을 자유롭게 지정하는 방식과, 같은 표현식의 값들을 비교하는 간단한 방식이 있다. 여러 조건에서 같은 값에 대한 동등 비교만 한다면 간단한 형식으로 쓸 수 있다.
- 부등호 등 복잡한 조건이 필요하면 일반적인
CASE형식을 사용한다.
DECODE는 오라클에서 제공하는 함수로, 주로 값의 동등 비교를 통한 조건 분기에 사용된다. 표준 SQL인CASE와 달리 오라클 특화 기능이다.
7. NULL 관련 함수
NULL은 값이 없음을 나타내며, 공백·0·빈 결과 집합과 다르다. NULL이 포함된 산술 연산의 결과는 NULL이 되므로 계산에 사용하는 열은 주의해서 처리해야 한다.
NVL(표현식, 대체값): 표현식이 NULL이면 대체값을, 아니면 원래 값을 반환한다. 두 값은 호환되는 데이터 형식이어야 한다.
NULLIF(표현식1, 표현식2): 두 표현식이 같으면 NULL, 다르면 첫 번째 표현식을 반환한다.
COALESCE(표현식1, 표현식2, ...): 왼쪽부터 확인해 NULL이 아닌 첫 번째 값을 반환한다. 모두 NULL이면 NULL을 반환한다.
NVL및 이와 유사한 NULL 처리 로직은CASE와IS NULL조건으로 표현할 수 있다.
- 계산에서 NULL을 대체할 값은 연산의 의미에 맞게 선택한다. 덧셈에서는 보통 0, 곱셈에서는 보통 1이 적절할 수 있다.
- 급여와 성과급을 더해 연봉을 계산할 때 성과급이 NULL이면 전체 결과도 NULL이 된다. 따라서 NULL을 적절한 값으로 대체해야 한다. 안전한 계산을 위해 각 입력 열의 NULL 여부를 확인하는 것이 좋다.