[SQL] 데이터 처리와 가공을 위한 오라클 함수_1

오라클 함수의 종류

오라클 함수는 함수를 제작한 주체를 기준으로 오라클에서 기본으로 제공하는 내장함수(built-in function)와 사용자가 필요해서 직접 정의한 사용자 정의 함수(user-defined fuction)로 나뉨.

내장 함수는 입력 방식에 따라 데이터 처리에 사용하는 행이 나뉘며, 데이터를 한 행씩 입력하고 입력한 행 마다 하나씩 결과가 나오는 함수를 단일행 함수(single-row function), 여러 행을 입력하여 하나의 행을 결과로 반환하는 함수를 다중행 함수(multiple-row function)라고 함.

 

문자열 데이터를 가공하는 문자 함수

 

대소 문자를 바꿔 주는 UPPER, LOWER, INITCAP 함수

함수	설명
UPPER(문자열)	괄호 안 문자열 데이터를 모두 대문자로 변환하여 반환
LOWER(문자열)	괄호 안 문자열 데이터를 모두 소문자로 변환하여 반환
INITCAP(문자열)	괄호 안 문자열 데이터 중 첫 글자는 대문자로, 나머지 문자는 소문자로 변환하여 반환

SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
	FROM EMP;

 

와일드카드 사용하기

SELECT *
	FROM 게시판 테이블
    WHERE 게시판 제목 열 LIKE '%Oracle%'
    OR 	  게시판 본문 열 LIKE '%Oracle%'

-- UPPER 함수로 문자열 비교하기(이름이 SCOTT인 데이터 찾기)
SELECT *
	FROM EMP
WHERE UPPER(ENAME) = UPPER('scott');


-- UPPER 함수로 문자열 비교하기(이름에 SCOTT 단어를 포함한 데이터 찾기)
SELECT *
	FROM EMP
WHERE UPPER(ENAME) LIKE UPPER ('%scott%');

---

문자열 길이를 구하는 LENGTH 함수

-- 선택한 열의 문자열 길이 구하기
SELECT ENAME, LENGTH(ENAME)
	FROM EMP;
    
    
-- 이름의 길이가 5 이상인 행 출력하기
SELECT ENAME, LENGTH(ENAME)
	FROM EMP
WHERE LENGTH(ENAME) >= 5;


-- LENGTH 함수와 LENGTHB 함수 비교하기
SELECT LENGTH('한글'), LENGTHB('한글')
	FROM DUAL;

---

문자열 일부를 추출하는 SUBSTR 함수

함수	설명
SUBSTR(문자열 데이터, 시작 위치, 추출 길이)	문자열 데이터의 시작 위치부터 추출 길이만큼 추출 시작 위치가 음수라면 마지막 위치부터 거슬러 올라간 위치에서 시작
SUBSTR(문자열 데이터, 시작 위치)	문자열 데이터의 시작 위치부터 문자열 데이터를 끝까지 추출 시작 위치가 음수라면 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출

SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5)
	FROM EMP;

-- SUBSTR 함수 안에 다른 함수(LENGTH) 함께 사용하기

SELECT JOB,
	SUBSTR(JOB, -LENGTH(JOB)),
    SUBSTR(JOB, -LENGTH(JOB), 2),
    SUBSTR(JOB, -3)
FROM EMP;

 

 

문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수

INSTR ([대상 문자열 데이터(필수)],
      [위치를 찾으려는 부분 문자(필수)],
      [위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1)],
      [시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1])

-- 특정 문자 위치 찾기


SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,
	   INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2,
       INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3
FROM DUAL;

-- 특정 문자를 포함한 행 찾기

SELECT *
	FROM EMP
WHERE INSTR(ENAME, 'S') > 0;


SELECT *
	FROM EMP
WHERE ENAME LIKE '%S%';

---

특정 문자를 다른 문자로 바꾸는 REPLACE 함수

-- REPLACE([문자열 데이터 또는 열 이름(필수)], [찾는 문자(필수)], [대체할 문자(선택)])

SELECT '010-1234-5678' AS REPLACE_BEFORE,
	REPLACE('010-1234-5678','-', '') AS REPLACE_1,
    REPLACE('010-1234-5678)', '-') AS REPLACE_2
FROM DUAL;

 

데이터의 공간을 특정 문자로 채우는 LPAD, RPAD함수

-- LPAD([문자열 데이터 또는 열 이름(필수)], [데이터의 자릿수(필수)], [공간에 채울 문자(선택)])
-- RPAD([문자열 데이터 또는 열 이름(필수)], [데이터의 자릿수(필수)], [공간에 채울 문자(선택)])

SELECT 'Oracle',
    LPAD('Oracle', 10, '#') AS LPAD_1,
    RPAD('Oracle', 10, '*') AS RPAD_1,
    LPAD('Oracle', 10) AS LPAD_2,
    RPAD('Oracle', 10) AS RPAD_2
FROM DUAL;

	1	2	3	4	5	6	7	8	9	10
LPAD('Oracle', 10, '#')	#	#	#	#	O	r	a	c	l	e
RPAD('Oracle', 10, '*')	O	r	a	c	l	e	*	*	*	*
LPAD('Oracle', 10)					O	r	a	c	l	e
RPAD('Oracle', 10)	O	r	a	c	l	e

---

두 문자열 데이터를 합치는 CONCAT 함수

SELECT CONCAT(EMPNO, ENAME),
	   CONCAT(EMPNO, CONCAT(' : ', ENAME))
  FROM EMP
WHERE ENAME = 'SCOTT';

* 문자열 데이터를 연결하는 || 연산자

SELECT EMPNO || ENAME,
       EMPNO || ' : ' || ENAME
 FROM ...

---

특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수

TRIM([삭제할 옵션(선택)] [삭제할 문자(선택)] FROM [원본 문자열 데이터(필수)])


-- TRIM 함수로 공백 제거하여 출력하기
SELECT '[' || TRIM('_ _Oracle_ _') || ']' AS TRIM.
       '[' || TRIM(LEADING FROM '_ _Oracle_ _') || ']' AS TRIM_LEADING,
       '[' || TRIM(TRAILING FROM '_ _Oracle_ _') || ']' AS TRIM_TRAILING,
       '[' || TRIM(BOTH FROM '_ _Oracle_ _') || ']' AS TRIM_BOTH
FROM DUAL;


-- TRIM 함수로 _삭제 후 출력하기
SELECT '[' || TRIM('_' FROM '_ _Oracle_ _') || ']' AS TRIM.
       '[' || TRIM(LEADING '_' FROM '_ _Oracle_ _') || ']' AS TRIM_LEADING,
       '[' || TRIM(TRAILING '_' FROM '_ _Oracle_ _') || ']' AS TRIM_TRAILING,
       '[' || TRIM(BOTH '_' FROM '_ _Oracle_ _') || ']' AS TRIM_BOTH
FROM DUAL;

 

LTRIM, RTRIM 함수의 기본 사용법

LTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])
-- 원본 문자열의 왼쪽에서 삭제할 문자열을 지정(삭제할 문자열을 지정하지 않으면 공백을 삭제함)

RTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])
-- 원본 문자열의 오른쪽에서 삭제할 문자열을 지정(삭제할 문자열을 지정하지 않으면 공백을 삭제함)

SELECT '[' || TRIM('_Oracle_') || ']' AS TRIM,
       '[' || LTRIM('_Oracle_') || ']' AS LTRIM,
       '[' || LTRIM('<_Oracle_>', '_<') || ']' AS LTRIM_2,
       '[' || RTRIM('_Oracle_') || ']' AS RTRIM,
       '[' || RTRIM('<_Oracle_>', '>_') || ']' AS RTRIM_2,

---

숫자 데이터를 연산하고 수치를 조정하는 숫자 함수

함수	설명
ROUND	지정된 숫자의 특정 위치에서 반올림한 값을 반환
TRUNC	지정된 숫자의 특정 위치에서 버림 한 값을 반환
CEIL	지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환
FLOOR	지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환
MOD	지정된 숫자를 나눈 나머지를 반환

 

특정 위치에서 반올림 하는 ROUND 함수

ROUND함수는 TRUNC함수와 함께 가장 자주 사용하는 숫자 함수이며, 특정 숫자를 반올림하며 반올림할 위치를 지정할 수 있음.

지정하지 않으면 소수 첫째 자리에서 반올림한 결과를 반환함.

ROUND([숫자(필수)], [반올림 위치(선택)])
-- 특정 숫자를 반올림한 결과를 출력할 때 사용하며, 반올림 위치를 지정하지 않으면 소수 첫째 자리에서 반올림 함.


SELECT ROUND(1234.5678) AS ROUND,
       ROUND(1234.5678,0) AS ROUND_0,
       ROUND(1234.5678,1) AS ROUND_1,
       ROUND(1234.5678, 2) AS ROUND_2,
       ROUND(1234.5678,-1) AS ROUND_MINUS1,
       ROUND(1234.5678,-2) AS ROUND_MINUS2
FROM DUAL;

1234.5678
자연수 둘째 자리 반올림	자연수 첫째 자리 반올림	소수 첫째 자리 반올림	소수 둘째 자리 반올림	소수 셋째 자리 반올림
-2	-1	0	1	2
1200	1230	1235	1234.6	1234.57

---

특정 위치에서 버리는 TRUNC 함수

TRUNC함수는 지정된 자리에서 숫자를 버림 처리하는 함수이며, 버릴 자리를 지정할 수 있고, 위치를 지정하지 않으면 소수 첫째 자리에서 버림 처리.

TRUNC([숫자(필수)], [버릴 위치(선택)])
-- 특정 위치에서 숫자를 버림 한 결과를 출력할 때 사용하며, 버림 위치를 지정하지 않으면 소수 첫째 자리에서 버림 함.

SELECT TRUNC(1234.5678) AS TRUNC,
       TRUNC(1234.5678,0) AS TRUNC_0,
       TRUNC(1234.5678,1) AS TRUNC_1,
       TRUNC(1234.5678, 2) AS TRUNC_2,
       TRUNC(1234.5678,-1) AS TRUNC_MINUS1,
       TRUNC(1234.5678,-2) AS TRUNC_MINUS2
FROM DUAL;

---

지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수

CEIL함수와 FLOOR함수는 각각 입력한 숫자와 가장 큰 정수, 가작은 정수를 반환하는 함수

-- CEIL([숫자(필수])
-- FLOOR([숫자(필수])

SELECT CEIL(3.14),
       FLOOR(3.14),
       CEIL(-3.14),
       FLOOR(-3.14)
FROM DUAL;

---

숫자를 나눈 나머지를 구하는 MOD함수

숫자 데이터를 다루다 보면 간혹 숫자 데이터를 특정 숫자로 나눈 나머지를 구해야 할 때가 생기는데, 오라클에서는 나머지를 구하는 함수를 제공함.

MOD([나머지를 구할 숫자(필수)], [나눌 숫자(필수)])
-- 특정 숫자를 나누고 그 나머지를 출력하는 함수

SELECT MOD(15,6),
       MOD(10,2),
       MOD(11,2)
FROM DUAL;