data/oracle

[데이터베이스실습] 04. 오라클 함수 - 문자 함수

렁치 2026. 7. 23. 15:30

연산자만으로는 다루기 어려운 데이터 가공을, 오라클은 다양한 내장 함수(built-in function)로 지원한다. 함수는 처리 단위에 따라 두 갈래로 나뉜다.

  • 단일행 함수(single-row function): 한 행마다 하나의 결과. 문자·숫자·날짜 함수가 여기 속한다.
  • 다중행 함수(multiple-row function): 여러 행을 묶어 하나의 결과. COUNT·SUM 같은 집계 함수다.

오늘은 그중 가장 자주 쓰는 문자 함수들을 예제와 함께 정리했다.

DUAL 테이블로 함수 테스트하기
함수 결과만 가볍게 확인하고 싶을 때, EMP 같은 실제 테이블에 대고 조회하면 14행이 똑같이 반복되어 번거롭다. 이럴 때 오라클이 제공하는 행 하나짜리 더미 테이블 DUAL을 쓰면 결과가 딱 한 줄 나온다.

SELECT LENGTH('HELLO') FROM DUAL;

DUAL을 처음 봤을 땐 "이 테이블은 뭐지" 싶었는데, 함수 결과만 한 줄로 확인하기에 이만큼 편한 게 없었다.

1. 대소문자 변환: UPPER, LOWER, INITCAP

함수 설명
UPPER(문자열) 모두 대문자로
LOWER(문자열) 모두 소문자로
INITCAP(문자열) 첫 글자만 대문자, 나머지는 소문자 
SELECT ename, UPPER(ename), LOWER(ename), INITCAP(ename) FROM emp;

데이터는 대소문자를 구분하므로, 저장된 모양을 모를 때 양쪽을 같은 케이스로 맞춰 비교하니 편했다.

SELECT * FROM emp WHERE UPPER(ename) = 'SCOTT';
SELECT * FROM emp WHERE UPPER(ename) LIKE '%SCO%';

2. 문자열 길이: LENGTH, LENGTHB

함수 설명
LENGTH(문자열) 길이(문자 수)
LENGTHB(문자열) 바이트 수 
SELECT ename, LENGTH(ename) FROM emp WHERE LENGTH(ename) >= 5;

LENGTH와 LENGTHB의 차이는 한글·이모지처럼 한 글자가 여러 바이트인 문자에서 드러난다. 글자 수를 셀 땐 LENGTH, 저장 공간이나 바이트 기준 제약을 다룰 땐 LENGTHB를 쓴다.

3. 추출·검색·대체: SUBSTR, INSTR, REPLACE

SUBSTR(원본문자열, 시작위치, 추출개수)   -- 일부 추출
INSTR(원본, 찾을문자열, 시작위치, 찾을순서)  -- 위치 반환 (없으면 0)
REPLACE(원본, 찾는문자열, 대체할문자열)    -- 문자 대체

SUBSTR는 시작 위치에 음수를 주면 뒤에서부터 센다는 점이 유용했다.

-- JOB을 여러 방식으로 잘라보기
SELECT JOB, SUBSTR(JOB,1,2), SUBSTR(JOB,3,2), SUBSTR(JOB,5), SUBSTR(JOB,-1) FROM EMP;

-- 이름의 끝 3자리: 음수 위치와 LENGTH 이용, 두 방법 모두 같은 결과
SELECT ENAME, SUBSTR(ENAME, -3), SUBSTR(ENAME, LENGTH(ENAME)-2) FROM EMP;

INSTR는 "찾을 순서"까지 지정할 수 있어, LIKE로는 까다로운 조건을 풀 수 있다.

SELECT INSTR('HELLO, ORACLE', 'E', 1, 2) FROM DUAL;  -- 1번째부터 세어 2번째 'E'의 위치

-- 이름에 A(또는 a)가 2개 이상인 사원
SELECT * FROM EMP WHERE INSTR(UPPER(ENAME), 'A', 1, 2) > 0;

REPLACE는 세 번째 인자를 생략하면 찾은 문자를 삭제한다.

SELECT REPLACE('010-1234-5678', '-', '_') AS R1,   -- 하이픈을 _로
       REPLACE('010-1234-5678', '-')      AS R2    -- 하이픈 삭제
FROM DUAL;

4. 채우기·연결·삭제: LPAD/RPAD, CONCAT, TRIM

LPAD / RPAD: 자리 채우기

지정한 자리수보다 데이터가 짧으면 특정 문자로 채운다. LPAD는 왼쪽, RPAD는 오른쪽을 채운다. 마스킹 처리에 자주 쓴다.

SELECT LPAD('Oracle', 10, '#') AS LPAD1,   -- ####Oracle
       RPAD('Oracle', 10, '#') AS RPAD1,   -- Oracle####
       LPAD('Oracle', 10)      AS LPAD2    -- 채울문자 생략 시 공백
FROM DUAL;

CONCAT / || : 문자열 연결

CONCAT은 인자가 둘뿐이라, 셋 이상을 이으려면 중첩해야 한다. 그래서 실무에서는 개수 제한이 없는 || 연산자를 더 많이 쓴다.

SELECT CONCAT(EMPNO, ENAME)          AS EMP1,
       EMPNO || ' : ' || ENAME       AS EMP2   -- || 가 더 유연
FROM EMP WHERE UPPER(ENAME) = 'SCOTT';

TRIM / LTRIM / RTRIM: 문자 삭제

TRIM은 양 끝에서 지정한 문자(생략 시 공백)를 삭제한다. LTRIM/RTRIM은 삭제할 문자 집합을 받아 한쪽 끝을 정리한다.

SELECT TRIM('_' FROM '_  _  ORACLE  _  _')          AS T_BOTH,
       TRIM(LEADING  '_' FROM '_ ORACLE _')          AS T_LEFT,
       TRIM(TRAILING '_' FROM '_ ORACLE _')          AS T_RIGHT
FROM DUAL;

-- 공백과 _ 를 한꺼번에 왼쪽에서 제거
SELECT LTRIM('_  _  ORACLE  _  _', ' _') FROM DUAL;

실습 쿼리로 익히기

함수들을 조합한 실전형 예제다.

-- 이름 길이가 5인 사원의 사번·이름 마스킹 (앞 일부만 남기고 * 로 채움)
SELECT EMPNO,
       RPAD(SUBSTR(EMPNO,1,2), 4, '*') AS MASK_EMPNO,
       ENAME,
       RPAD(SUBSTR(ENAME,1,1), 5, '*') AS MASK_ENAME
FROM EMP
WHERE LENGTH(ENAME) = 5;

-- 월급으로 일급·시급 계산 (월 21.5일, 1일 8시간 가정)
SELECT EMPNO, ENAME, SAL,
       TRUNC(SAL/21.5, 2)   AS DAY_PAY,    -- 소수 둘째 자리까지 버림
       ROUND(SAL/21.5/8, 1) AS TIME_PAY    -- 소수 첫째 자리로 반올림
FROM EMP;

마스킹 예제는 SUBSTR로 앞부분만 잘라낸 뒤 RPAD로 나머지를 *로 채우는, 문자 함수 조합의 전형이다. 함수는 이렇게 중첩해서 쌓을 때 진짜 위력이 나온다는 걸 직접 짜보고 알았다.

수업에서 직접 친 쿼리

INSTR로 "특정 문자가 2개 이상"을 찾는 게 인상 깊었다. LIKE로는 까다로운 조건인데 INSTR의 "찾을 순서" 인자로 한 번에 풀렸다.

-- 1번째부터 세어 2번째 'E'의 위치
SELECT INSTR('HELLO, ORACLE', 'E', 1, 2) FROM DUAL;

-- 이름에 A(또는 a)가 2개 이상 포함된 사원
SELECT * FROM EMP WHERE INSTR(UPPER(ENAME), 'A', 1, 2) > 0;

REPLACE는 전화번호 문자열로 여러 변형을 쳐봤고, 세 번째 인자를 빼면 삭제된다는 것도 확인했다.

SELECT REPLACE('010-1234-1234', '-', '_')  AS REPLACE1,   -- 하이픈을 _로
       REPLACE('010-1234-5678', '1234', '5678') AS REPLACE2,
       REPLACE('010-1234-5678', '-')      AS REPLACE3     -- 하이픈 삭제
FROM DUAL;

CONCAT은 인자가 둘뿐이라 셋 이상은 중첩해야 했고, 결국 ||가 편하다는 걸 직접 비교하며 알았다.

SELECT CONCAT(EMPNO, CONCAT(':', ENAME)) AS EMP2,   -- 중첩해야 함
       EMPNO || ' : ' || ENAME           AS EMP3    -- || 가 훨씬 유연
FROM EMP WHERE UPPER(ENAME) = 'SCOTT';

TRIM은 LEADING/TRAILING/BOTH와 삭제할 문자 집합을 바꿔가며 한참 실험했다. 특히 LTRIM/RTRIM에 문자 집합을 주면 그 집합에 속한 문자를 한쪽 끝에서 다 깎아낸다는 걸 직접 보고 이해했다.

SELECT TRIM(LEADING  '_' FROM '_  _  ORACLE  _  _') AS TRIM_L,
       LTRIM('_  _  ORACLE  _  _', ' _')            AS LTRIM   -- 공백과 _를 함께
FROM DUAL;

오늘 느낀 점

  • 함수를 하나씩 외우기보다 "SUBSTR로 자르고 RPAD로 채운다"처럼 조합해보니, 마스킹 같은 실전 요구가 자연스럽게 풀렸다. 함수는 중첩할 때 의미가 살았다.
  • || 연결은 NULL을 무시하는데 산술 연산은 NULL이면 통째로 NULL이 되는 정반대 동작이 헷갈렸다. 둘을 같이 적어두니 구분이 됐다.
  • INSTR가 "찾을 순서"까지 받는다는 점에서, LIKE로 안 되던 "특정 문자 2개 이상" 같은 조건도 풀 수 있어 도구의 폭이 넓어졌다.

한 걸음 더

  • INSTR가 "없으면 0을 반환"한다는 점은 단순해 보여도 실무에서 요긴하다. WHERE INSTR(ename,'S') > 0은 "S가 들어간 행"을 뜻하는데, 이는 LIKE '%S%'와 같은 결과다. 다만 INSTR는 "몇 번째 S냐"까지 지정할 수 있어, "특정 문자가 2개 이상 있는 행" 같은 LIKE로는 어려운 조건을 풀 수 있다.
  • 문자열을 다룰 때 || 연결 연산자는 NULL을 만나면 그 부분을 빈 문자열처럼 취급해 무시한다. 산술 연산에서 NULL이 결과 전체를 NULL로 만드는 것과 정반대라, 둘을 헷갈리지 않도록 주의한다.
  • 위 함수들은 표준 SQL이 아니라 오라클 방언이 많이 섞여 있다. 예를 들어 SUBSTR은 다른 DBMS에도 있지만 INSTR·LPAD의 인자 순서나 동작은 제품마다 조금씩 다르다. "이건 오라클 함수"라는 의식을 갖고, 다른 DB로 옮길 땐 해당 문서를 확인하는 습관이 안전하다.
저작자표시 비영리 변경금지 (새창열림)