[Oracle DB] 오라클 함수 (1) - 단일행 문자 함수 (UPPER, LOWER, INITCA, LENGTH, SUBSTR, INSTR, REPLACE, LPAD, RPAD, CONCAT, TRIM, LTRIM, RTRIM )

 

오라클에서는 연산자만으로 다루기 어려운 복잡한 데이터 처리와 다양한 결과를 얻기 위해 많은 함수를 제공한다.

이러한 함수는 이전 포스팅에서 살펴보았던 다른 연산자들과 마찬가지로 WHERE절에서 조회할 행을 선별하는 데 사용할 수 있다. 또 SELECT절에서 데이터를 원하는 형태로 가공하거나 의미 있는 값을 출력할 때에도 많이 사용한다.

 

---

 

오라클 함수의 종류

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

 

 

 

내장 함수

내장 함수는 입력 방식에 따라 데이터 처리에 사용하는 행이 나뉜다. 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수를 단일행 함수(single-row function)라고 한다.

 

반면 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수를 다중행 함수(multiple-row function)라고 한다.

 

 

---

 

단일행 함수 - 문자 함수

문자 함수는 문자 데이터를 가공하거나 문자 데이터로부터 특정 결과를 얻고자 할 때 사용한다.

 

 

 

---

 

UPPER, LOWER, INITCAP

문자열의 대문자, 소문자를 바꾸어 주는 함수들이다.

 

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

 

사용 예시는 다음과 같다.

SELECT NAME, UPPER(NAME), LOWER(NAME), INITCAP(NAME)
FROM STUDENT;

NAME	UPPER(NAME)	LOWER(NAME)	INITCAP(NAME)
SHIN	SHIN	shin	Shin

 

 

UPPER, LOWER, INITCAP 함수를 사용하려면 입력 데이터에 열 이름이나 데이터를 직접 지정해야 한다.

 

이를 어디에 사용할 수 있을까?

 

검색창을 하나 생각해 보자. 제목이나 본문에 Shin 문자열이 들어간 데이터를 검색하는 기능을 구현한다면, 아마 다음과 같이 사용할 것이다.

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

그런데 이렇게 되면 SHIN이나 shin등은 조회되지 않는다.

이때 조건식 양 쪽의 데이터를 모두 대문자나 소문자로 바꾸어 비교한다면 실제 검색어의 대, 소문자 여부와 상관없이 찾을 수 있게된다.

 

다음과 같이 사용할 수 있다는 말이다.

SELECT *
FROM 테이블
WHERE UPPER(게시판 제목 열) LIKE UPPER('%Shin%')
OR UPPER(게시판 본문 열) LIKE UPPER('%Shin%');

 

 

 

---

 

LENGTH

특정 문자열의 길이를 구할 때 사용되는 함수이다.

사용 방법은 위에서 배운 UPPER, LOWER 등과 같이 괄호 안에 문자열을 넣으면 된다.

다양한 사용 예시를 통해 LENGTH에 대해 알아보자.

 

 

 

 

 

LENGTH함수 사용하기 - 기본

SELECT NAME, LENGTH(NAME)
FROM STUDENT;

 

결과

NAME	LENGTH(NAME)
SHIN	4
DONG	4
HUN	3

 

 

 

 

 

 

WHERE절에서 LENGTH 사용하기

SELECT NAME, LENGTH(NAME)
FROM STUDENT
WHERE LENGTH(NAME) > 3;

 

결과

NAME	LENGTH(NAME)
SHIN	4
DONG	4

 

 

 

 

LENGTH 함수와 LENGTHB 함수 비교하기

LENGTHB함수는 LENGTH함수와 사용 방식은 같으나 문자열 데이터 길이가 아닌 바이트 수를 반환한다.

영어는 글자당 1바이트이고, 한글은 글자당 2바이트기 때문에, 한글을 사용할 때 차이가 난다.

 

SELECT LENGTH('한글'), LENGTHB('한글')
FROM DUAL;

 

결과

LENGTH('한글')	LENGTHB('한글')
2	4
2	4

 

(참고로  DUAL 테이블은 오라클 자체에서 제공되는 테이블로 간단하게 함수를 이용해서 계산 결과값을 확인할 때 사용한다.)

 

 

 

---

 

SUBSTR

문자열 중 일부만 추출할 때 사용한다.

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

(참고로 처음 위치는 0이 아니라 1이다!)

 

사용 예시는 다음과 같다.

SELECT NAME, SUBSTR(NAME, 1, 2), SUBSTR(NAME, 3, 2), SUBSTR(NAME, 5)

 

결과

NAME	SUBSTR(NAME, 1, 2)	SUBSTR(NAME, 3, 2)	SUBSTR(NAME, 5)
SHIND	SH	IN	D

 

 

 

---

 

 

INSTR

문자열 안에 특정 문자나 문자열이 어디에 포함되어 있는지를 알고자 할 때 사용한다.

INSTR 함수는 총 4개의 입력 값을 지정할 수 있으며, 최소 두 개의 입력값, 즉 원본 문자열과 원본 문자열에서 찾으려는 문자 2가지는 필수로 지정해 주어야 한다.

 

 

기본 형식

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;

 

INSTR_1	INSTR_2	INSTR_3
3	12	4

 

표를 가지고 설명해 보도록 하겠습니다.

 

 

 

1 - INSTR('HELLO, ORACLE!', 'L') AS INSTR_1 : 시작 위치와 몇 번째 L인지 정해지지 않음

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	!

 

 

2 - INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2 : 다섯 번째 글자 O부터 L을 찾음

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	!

 

 

3 - INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3 : 두 번째 글자 E부터 시작해서 두 번째로 등장하는 L을 찾음

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 함수의 세 번째 입력 데이터, 즉 위치 찾기를 시작하는 위치 값에 음수를 쓸 때 원본 문자열의 오른쪽 끝부터 왼쪽 방향으로 검색한다. 만약 찾으려는 문자가 문자열 데이터에 포함되어 있지 않다면 위치 값이 없으므로 0을 반환한다.

 

 

 

---

 

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;

REPLACE_BEFORE	REPLACE_1	REPLACE_2
010-1234-5678	010 1234 5678	01012345678

 

 

 

---

 

LPAD, RPAD

데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우에 나머지 공간을 특정 문자로 채우는 함수이다.

LPAD와 RPAD는 각각 Left Padding(왼쪽 패딩), Right Padding(오른쪽 패딩)을 뜻한다. 

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;

 

 

결과

'ORACLE'	LAPD_1	RAPD_1	LAPD_1	RAPD_2
Oracle	####Oracle	Oracle****	Oracle	Oracle

 

 

---

 

CONCAT

두 개의 문자열을 하나로 연결해 주는 함수이다.

연결할 두개의 입력 데이터를 지정할 때, 열이나 문자열 데이터 모두 지정할 수 있다.

 

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

CONCAT(EMPNO, ENAME)	CONCAT(EMPNO, CONCAT(' : ', ENAME))
7788SCOTT	7788 : SCOTT

 

 

추가로 || 연산자는 문자열을 연결한다.

CONCAT과 유사하며, 위의 예시는 ||를 사용하면 다음과 같다.

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

 

 

---

 

TRIM, LTRIM, RTRIM

문자열 내에서 특정 문자를 지울 때 사용하는 함수이다.

삭제하려는 문자열을 지정해주지 않으면 기본적으로 공백을 제거한다.

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

FROM은 앞에 두 옵션이 없다면 사용하지 않으며, 삭제 옵션에는 왼쪽에 있는 글자를 지우는 LEADING, 오른쪽 글자를 지우는 TRAILING, 양쪽 문자를 지우는 BOTH가 있다.

 

 

사용 예시는 다음과 같다.

 

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	TRIM_LEADING	TRIM_TRAILING	TRIM_BOTH
[_ _Oracle_ _]	[_ _Oracle_ _ ]	[ _ _Oracle_ _]	[_ _Oracle_ _]

 

 

 

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;

 

결과

TRIM	TRIM_LEADING	TRIM_TRAILING	TRIM_BOTH
[ _Oracle_ ]	[ _Oracle_ _ ]	[ _ _Oracle_ ]	[ _ _Oracle_ _ ]

 

 

LTRIM, RTRIM 사용법

LTRIM, RTRIM 함수는 각각 왼쪽, 오른쪽의 지정 문자를 삭제하는 데 사용한다.

TRIM과 다른 점은 삭제할 문자를 여러개 지정할 수 있다는 것이다.

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
FROM DUAL;

 

결과

TRIM	LTRIM	LTRIM_2	RTRIM	RTRIM_2
[_Oracle_]	[_Oracle_ ]	[Oracle_>]	[ _Oracle_]	[<_Oracle]

 

LTRIM, RTRIM을 사용한 예시에서 삭제 대상이 문자일 경우 해당 문자의 순서와 반복을 통해 만들어 낼 수 있는 모든 조합이 각각 왼쪽, 오른쪽부터 삭제되어간다.

LTRIM_2 의 경우 <_ 문자열은 _, < 문자의 조합으로 표현 가능한 문자이므로 삭제된다.

하지만 그다음에 이어지는 Oracle의 O 문자는 _ <로 조합할 수 없는 문자가 시작되므로 이 단계에서 LTRIM을 통한 작업은 끝난다.

예를 하나 더 들자면 만약 원본 문자열이 <_<_Oracle인 경우에는 Oracle만 남게 되고, <_O<_racle인 경우에는 O<_racle이 남게 된다.