6-1 오라클 함수와 문자 함수

데이터베이스/Oracle

6-1 오라클 함수와 문자 함수

Se On 2024. 1. 17. 17:26

오라클 함수

1. 정의

목적: 특정 결과 데이터를 얻기 위해 사용

과정: 어떤 값이나 데이터를 입력 → 그 값에 따라 가공 또는 연산의 과정을 거침 → 결과 값이 나옴

2. 종류

내장 함수: 이미 모두 만들어져 있는 상태이므로 원하는 기능을 간편하게 바로 사용할 수 있음

사용자 정의 함수: 구성 요소의 선별과 제작에 시간이 필요하므로 바로 사용은 어려움, 하지만 내 입맛에 맞게 적절한 기능을 직접 구현해서 사용할 수 있음

내장 함수의 종류

단일행 함수: 데이터가 한 행씩 입력 → 한 행당 결과가 하나씩 나옴

다중행 함수: 데이터가 여러 행이 입력 → 하나의 행으로 결과가 반환됨

6장에서는 단일행 함수부터 살펴볼 예정

문자 함수

1. UPPER, LOWER, INITCAP 함수

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

*INITCAP: Initial Capitalization

검색하는 기능 구현 시 LIKE 연산자와 와일드 카드를 함께 사용할 수 있는데 만약 문자열 데이터의 패턴을 %ORACLE%로 지정하면 'ORACLE' 문자열이 포함된 데이터가 출력됨

즉, 위 패턴은 Oracle, oracle, OrAcLe 등과 같이 대소문자가 다른 여러 경우를 찾아내지는 못함

이때 조건식 양쪽 항목의 문자열 데이터를 모두 대문자(UPPER)나 소문자(LOWER)로 바꿔서 비교한다면 검색어의 대소문자 여부와 관계없이 검색 단어와 일치한 문자열을 포함한 데이터를 찾을 수 있음

-- 대소문자 상관없이 scott인 사람 찾아보기
SELECT *
 FROM EMP
WHERE UPPER(ENAME) = UPPER('scott');

-- 대소문자 상관없이 사원 이름에 scott이 들어간 사람 찾아보기
SELECT *
 FROM EMP
WHERE UPPER(ENAME) LIKE UPPER('%scott%');

-- 사원 이름을 소문자로 출력하기
SELECT LOWER(ENAME)
 FROM EMP;

-- 사원 이름을 첫 번째 글자만 대문자, 다음 글자는 소문자로 출력하기
SELECT INITCAP(ENAME)
 FROM EMP;

2. LENGTH, LENGTHB 함수

함수	설명
LENGTH(문자열)	특정 문자열의 길이를 구함
LENGTHB(문자열)*	특정 문자열의 바이트 수를 반환함

*유니코드를 표현하는 인코딩 방식에 따라 한글의 한 문자당 바이트 수는 상이함 (UTF-8**: 3 byte, UTF16***: 2 byte)

두 방식의 기본 차이는 문자 하나를 표현할 때 사용하는 최소 byte를 의미함

**UTF-8: 가변 길이 인코딩 방식으로 문자에 따라 메모리 사용량이 달라짐

문자 길이에 따라 1~4 byte까지 다양한 길이로 인코딩됨

영어와 기본 ASCII 문자는 1 byte로 표현되지만 한글과 같은 멀티바이트 문자는 3바이트로 인코딩됨

일반적으로 웹에서는 UTF-8이 더 널리 사용됨

***UTF-16: 고정 길이 인코딩 방식, 모든 문자를 2 혹은 4 byte로 인코딩함

모든 문자는 2 byte로 표현되지만 BMP(기본 다국어 평면)에 속하지 않는 문자는 대체로 4 byte로 표현됨

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

-- LENGTH 함수와 LENGTHB 함수 비교하기
SELECT LENGTH('한글'), LENGTHB('한글')
 FROM DUAL;
 
 -- 직책 이름이 6글자 이상인 데이터만 출력하기
 SELECT *
  FROM EMP
 WHERE LENGTH(JOB) >= 6;

LENGTH 함수와 LENGTHB 함수 비교 질의 결과 (UTF-8 방식으로 한 문자당 3바이트로 처리되어 '한글' 은 총 6바이트임)

3. SUBSTR 함수

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

-- SUBSTR 함수 사용하기
SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5), SUBSTR(JOB, -5, 1), SUBSTR(JOB, -5)
 FROM EMP;
 
-- 모든 사원 이름을 세 번째 글자부터 끝까지 출력하기
SELECT SUBSTR(ENAME, 3)
 FROM EMP;

CLERK의 경우 SUBSTR 함수에서 시작 위치: 1, 추출 길이: 2를 하면

첫 번째 문자인 C에서 시작하여 두 문자를 추출하는 것이므로 'CL'이 인출됨

-5	-4	-3	-2	-1
C	L	E	R	K
1	2	3	4	5

시작 위치: 3, 추출 길이: 2를 하면 세 번째 문자인 E에서 시작하여 두 문자를 추출하는 것이므로 'ER'이 인출됨

-5	-4	-3	-2	-1
C	L	E	R	K
1	2	3	4	5

시작 위치: 5, 추출 길이를 정하지 않으면 시작 위치에서부터 끝까지 추출하는 것이므로 'K'가 인출됨

-5	-4	-3	-2	-1
C	L	E	R	K
1	2	3	4	5

시작 위치가 음수일 경우 가장 오른쪽 문자가 -1, 가장 왼쪽 문자가 -N에 해당함

시작 위치: -5, 추출 길이: 1로 하면 -5에 해당하는 C에서 시작해 한 문자만 추출하므로 'C'만 인출됨

-5	-4	-3	-2	-1
C	L	E	R	K
1	2	3	4	5

시작 위치: -5, 추출 길이를 정하지 않으면 시작 위치에서부터 끝까지 추출하는 것이므로 'CLERK'이 인출됨

-5	-4	-3	-2	-1
C	L	E	R	K
1	2	3	4	5

SUBSTR 함수 안에 LENGTH 함수를 함께 사용하는 경우도 종종 있음

-- SUBSTR 함수 안에 LENGTH 함수 함께 사용하기
SELECT JOB, SUBSTR(JOB, -LENGTH(JOB)), SUBSTR(JOB, -LENGTH(JOB), 2), SUBSTR(JOB, -3)
 FROM EMP;

부분 문자열을 추출할 때 바이트 수로 시작 위치나 길이를 지정할 수 있는 SUBSTRB 함수도 존재함

UTF-8 인코딩 방식에서 한글은 한 문자당 3 byte기 때문에

SUBSTRB('한글', 1, 3) 최소 추출 길이를 3으로 해야 '한'이 출력됨 (그 미만은 출력되는 값이 없음)

-- SUBSTR 함수와 SUBSTRB 함수 사용하기
SELECT SUBSTR('한글', 1, 1), SUBSTR('한글', 1, 2),
       SUBSTRB('한글', 1, 1), SUBSTRB('한글', 1, 3), SUBSTRB('한글', 1, 6)
 FROM DUAL;

4. INSTR 함수

함수	설명
INSTR(대상 문자열 데이터, 위치를 찾으려는 부분 문자)	필수 값만 입력한 경우 대상 문자열 데이터에서 부분 문자의 위치를 찾아서 출력함
INSTR(대상 문자열 데이터, 위치를 찾으려는 부분 문자, 위치 찾기를 시작할 대상 문자열 데이터 위치, 시작 위치에서 찾으려는 문자가 몇 번째인지 지정)	필수 값과 옵션 값을 모두 입력한 경우 (옵션의 기본값: 1) 대상 문자열 데이터에서 부분 문자의 위치를 찾아서 출력하는 것은 동일하나, 위치 찾기를 시작할 지점과 찾으려는 문자가 몇 번째인지 지정할 수 있음

-- INSTR 함수로 문자열 데이터에서 특정 문자열 찾기
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;

INSTR_1) INSTR 함수에서 위치를 찾으려는 문자: 'L'을 하면

1번 위치에서부터 우측으로 이동해 해당 문자의 위치를 찾기 때문에 3이 인출됨

1	2	3	4	5	6	7	8	9	10	11	12	13	14
H	E	L	L	O	,		O	R	A	C	L	E	!

INSTR_2) 위치를 찾으려는 문자: 'L', 시작 위치: 5로 하면

5번 위치에서부터 우측으로 이동해 해당 문자의 위치를 찾기 때문에 12가 인출됨

1	2	3	4	5	6	7	8	9	10	11	12	13	14
H	E	L	L	O	,		O	R	A	C	L	E	!

INSTR_3) 위치를 찾으려는 문자: 'L', 시작 위치: 2, 찾으려는 문자의 순서: 2번째로 하면

2번 위치에서부터 우측으로 이동해 해당 문자의 두번째 위치를 찾기 떄문에 4가 인출됨

1	2	3	4	5	6	7	8	9	10	11	12	13	14
H	E	L	L	O	,		O	R	A	C	L	E	!

-- INSTR 함수로 문자열 데이터에서 특정 문자열 찾기 (음수)
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;

INSTR_3) 위치를 찾으려는 문자: 'L', 시작 위치: -2, 찾으려는 문자의 순서: 2번째로 하면

-2번 위치에서부터 왼쪽으로 이동해 해당 문자의 두번째 위치를 찾기 떄문에 4가 인출됨

1	2	3	4	5	6	7	8	9	10	11	12	13	14
H	E	L	L	O	,		O	R	A	C	L	E	!
-14	-13	-12	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1

LIKE 연산자와 INSTR 함수를 활용해 특정 문자를 포함하고 있는 행을 찾을 수 있음

-- INSTR 함수 사용하여 사원 이름에 문자 S가 있는 행 구하기
SELECT *
 FROM EMP
WHERE INSTR(ENAME, 'S') > 0;
-- 위치 값이 0을 초과해야 문자열 데이터에 S가 포함되어 있다는 뜻이므로 '> 0'를 입력하는 것임

-- LIKE 연산자를 사용하여 사원 이름에 문자 S가 있는 행 구하기
SELECT *
 FROM EMP
WHERE ENAME LIKE '%S%';

INST 함수 / LIKE 연산자로 사원 이름에 문자 S가 있는 행 구하기 질의 결과

5. REPLACE 함수

함수	설명
REPLACE(문자열 데이터 또는 열 이름, 찾는 문자)	필수 값만 입력한 경우 찾는 문자로 지정한 문자는 문자열 데이터에서 삭제함
REPLACE(문자열 데이터 또는 열 이름, 찾는 문자, 대체할 문자)	필수 값과 옵션 값을 모두 입력한 경우 찾는 문자로 지정한 문자는 대체할 문자로 바뀜

-- REPLACE 함수로 문자열 안에 있는 특정 문자 바꾸기
SELECT '010-1111-1111' AS REPLACE_BEFORE,
	   REPLACE('010-1111-1111', '-') AS REPLACE_1,
       REPLACE('010-1111-1111', '-', ' ') AS REPLACE_2
 FROM DUAL;

6. LPAD, RPAD 함수

함수	설명
LPALD(문자열 데이터 또는 열이름, 데이터의 자릿수, 빈 공간에 채울 문자 (선택))	데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우 남은 빈 공간을 왼쪽에 채움 빈 공간을 채울 문자를 지정하지 않은 경우 공백 문자로 띄움
RPALD(문자열 데이터 또는 열이름, 데이터의 자릿수, 빈 공간에 채울 문자 (선택))	데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우 남은 빈 공간을 오른쪽에 채움 빈 공간을 채울 문자를 지정하지 않은 경우 공백 문자로 띄움

-- LPAD, RPAD 함수 사용하기
SELECT 'Oracle',
       LPAD('Oracle', 10) AS LPAD_1,
       LPAD('Oracle', 10, '#') AS LPAD_2,
       RPAD('Oracle', 10) AS RPAD_1,
       RPAD('Oracle', 10, '$') AS RPAD_2
 FROM DUAL;

문자열 데이터를 특정 문자로 채우는 패딩 처리는 데이터의 일부만 노출해야 하는 개인정보를 출력할 때 아래와 같이 사용하기도 함

-- RPAD 함수 사용하여 개인정보 뒷자리 * 표시로 출력하기
SELECT RPAD('990528-', 14, '*') AS RPAD_IDNO,
       RPAD('010-1111-', 13, '*') AS RPAD_MNO
    FROM DUAL;

7. CONCAT 함수

두 개의 문자열 데이터를 하나로 연결함

|| 연산자: CONCAT 함수와 유사하게 열이나 문자열을 연결함

-- CONCAT 함수를 사용하여 문자열 연결하기
SELECT CONCAT(EMPNO, ENAME),
       CONCAT(EMPNO, CONCAT(' : ', ENAME))
 FROM EMP
 WHERE ENAME = 'SCOTT';

8. TRIM, LTRIM, RTRIM 함수

TRIM: 문자열 데이터 내에서 특정 문자를 지우기 위해 사용

함수	설명
TRIM(삭제 옵션(선택) 삭제할 문자(선택) FROM 원본 문자열 데이터(필수))	삭제할 문자가 없을 땐 공백이 제거되고, 삭제할 문자가 있을 땐 해당 문자가 제거됨 (공백 제거 X)
삭제 옵션	설명
LEADING	왼쪽에 있는 글자를 지움
TRAILING	오른쪽에 있는 글자를 지움
BOTH	양쪽 글자를 모두 지움

-- TRIM 함수로 공백 제거하기
SELECT '[' || TRIM(' _ _Oracle_ _ ') || ']' AS TRIM,
       '[' || TRIM(LEADING FROM ' _ _Oracle_ _ ') || ']' AS TRIM_L,
       '[' || TRIM(TRAILING FROM ' _ _Oracle_ _ ') || ']' AS TRIM_T,
       '[' || TRIM(BOTH FROM ' _ _Oracle_ _ ') || ']' AS TRIM_B
    FROM DUAL;

-- TRIM 함수로 문자 삭제하기
SELECT '[' || TRIM('_' FROM '_ _Oracle_ _') || ']' AS TRIM,
       '[' || TRIM(LEADING '_' FROM '_ _Oracle_ _') || ']' AS TRIM_L,
       '[' || TRIM(TRAILING '_' FROM '_ _Oracle_ _') || ']' AS TRIM_T,
       '[' || TRIM(BOTH '_' FROM '_ _Oracle_ _') || ']' AS TRIM_B
    FROM DUAL;

LTRIM, RTRIM: 삭제 대상이 문자일 경우 해당 문자의 순서와 반복을 통해 만들어 낼 수 있는 모든 조합이 각각 왼쪽, 오른쪽에서 삭제됨

함수	설명
LTRIM(원본 문자열 데이터, 삭제할 문자 집합(선택))	왼쪽에서 삭제할 문자열 지정함 (미지정 시 공백이 삭제됨)
RTRIM(원본 문자열 데이터, 삭제할 문자 집합(선택))	오른쪽에서 삭제할 문자열 지정함 (미지정 시 공백이 삭제됨)

-- TRIM 함수로 문자 삭제하기
SELECT '[' || TRIM(' _Oracle_ ') || ']' AS TRIM,
       '[' || LTRIM(' _Oracle_ ') || ']' AS LTRIM,
       '[' || RTRIM(' _Oracle_ ') || ']' AS RTRIM,
       '[' || LTRIM('<_Oracle_>', '_<') || ']' AS LTRIM_2,
       '[' || RTRIM('<_Oracle_>', '>-') || ']' AS RTIMR_2
    FROM DUAL;

참고 도서: https://www.yes24.com/Product/Goods/65849798

Do it! 오라클로 배우는 데이터베이스 입문 - 예스24

기본기가 무엇보다 중요한 데이터베이스처음 공부할 때 탄탄한 기본기를 쌓자!데이터베이스는 탄탄한 기본기를 쌓아야만 실무에서 어려운 SQL문을 만나도 흔들리지 않는다. 『Do it! 오라클로 배

1	2	3	4	5	6	7	8	9	10	11	12	13	14
H	E	L	L	O	,		O	R	A	C	L	E	!
-14	-13	-12	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1

1	2	3	4	5	6	7	8	9	10	11	12	13	14
H	E	L	L	O	,		O	R	A	C	L	E	!
-14	-13	-12	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1

1	2	3	4	5	6	7	8	9	10	11	12	13	14
H	E	L	L	O	,		O	R	A	C	L	E	!
-14	-13	-12	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1