[Oracle] Oracle Function(오라클 함수) - 문자 함수

들어가기 전에

이 포스팅은 오라클로 배우는 데이터베이스 입문(이지훈 저)을 읽으면서 기억하고자 하는 내용 및 추가적으로 알아본 내용을 담았습니다. 책 전체 내용을 보시고 싶으신 분은 책을 구매하시어 읽어보시길 바랍니다.

오라클 함수

오라클 함수란, 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어를 의미합니다. 오라클 함수는 함수를 제작한 주체를 기준으로 크게 2가지로 나뉩니다.

• 내장 함수(built-in function): 오라클에서 기본으로 제공하고 있는 함수
  • 내장 함수는 입력 방식에 따라 데이터 처리에 사용하는 행이 나뉩니다.
  • 단일행 함수(single-row function): 데이터가 한 행씩 입력되고, 입력된 각 행별로 결과가 하나씩 나오는 함수
  • 다중행 함수(multiple-row function): 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수
• 사용자 정의 함수(user-defined function): 사용자가 필요에 의해 직접 정의한 함수

단일행 함수와 다중행 함수(오라클로 배우는 데이터베이스 입문, 129p)

이번 시간에는 Oracle에서 제공하는 내장 함수 중 단일행 함수인 문자 함수에 대해 알아보도록 하겠습니다.

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

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

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

UPPER, LOWER, INITCAP 사용 예시

가변 길이 문자열 데이터에서 특정 문자열을 포함하는 데이터를 조회할 때 대/소문자로 변환하는 문자 함수가 유용합니다.

예를 들어, oracle이라는 단어가 들어간 데이터를 찾을 때 데이터 안에 대/소문자가 구분되어 들어가 있다면 '%oracle%' 뿐 아니라 '%Oracle%', '%ORACLE%' 등의 단어도 LIKE 구문을 통해 함께 검색해야 합니다. 이때, 아래와 같이 UPPER 함수를 이용해 문자를 비교하면 간단하게 작성 가능합니다.

SELECT    *
FROM      TABLE
WHERE     UPPER(DBNAME) LIKE UPPER('%oracle%')'

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

함수	설명
LENGTH(문자열)	괄호 안 컬럼의 데이터 또는 해당 문자열의 길이 반환
LENGTHB(문자열)	괄호 안 컬럼의 데이터 또는 해당 문자열의 바이트(byte) 수 반환

LENGTH() 함수와 LENGTHB() 함수가 서로 다른 결과 보여줌 확인

• 필자가 사용하는 Oracle Database의 charset은 AL32UTF8로 설정되어 있어, 한글이 3byte로 인식됩니다.
• DUAL 테이블은 오라클의 최고 권한 관리자 계정인 SYS 소유의 테이블로 SCOTT 계정도 사용할 수 있는 더미(dummy) 테이블입니다. 데이터 저장 공간이 아닌 임시 연산이나 함수의 결과 값 확인 용도로 종종 사용합니다.

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

함수	설명
SUBSTR(문자열 데이터, 시작 위치, 추출 길이)	문자열 데이터의 시작 위치부터 추출 길이만큼 추출 시작 위치가 음수라면, 마지막 위치부터 해당 값만큼 거슬러 올라간 위치에서 시작 예) -1이라면, 마지막 위치이며 -2이면 마지막 위치 바로 앞 문자
SUBSTR(문자열 데이터, 시작 위치)	문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출 시작 위치가 음수라면, 마지막 위치부터 해당 값만큼 거슬러 올라간 위치에서 시작 예) -1이라면, 마지막 위치이며 -2이면 마지막 위치 바로 앞 문자

SUBSTR() 함수 사용 예시

• SUBSTR(JOB, 1, 3): 첫 번째 문자부터 세 번째 문자까지 추출
• SUBSTR(JOB, -2, 3): 뒤에서 세번째 문자와 그 다음 문자까지 추출
• SUBSTR(JOB, -LENGTH(JOB), 2): JOB 컬럼의 길이의 음수값부터 2자리 문자 추출(첫 번째 문자와 두 번째 문자 추출)

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

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

INSTR 함수의 경우 문자열 데이터 안에 특정 문자나 문자열이 어느 위치에 포함되어 있는지 알고자 할 때 사용합니다.

• 기본적으로 찾고자 하는 대상 문자의 위치를 반환하며, 만약 해당 문자가 존재하지 않으면 0을 반환합니다.

INSTR() 함수 사용 예시

• INSTR('HELLO ORACLE', 'L'): 문자열의 첫 위치인 H부터 시작해 첫 번째로 나온 L 위치 반환
• INSTR('HELLO ORACLE', 'L', 5): 문자열의 다섯번째 위치인 O부터 시작해 첫 번째로 나온 L 위치 반환
• INSTR('HELLO ORACLE', 'L', 2, 2): 문자열의 첫 번째 위치인 E부터 시작해 두 번째로 나온 L 위치 반환
• INSTR('HELLO ORACLE', 'L', -1): 마지막 문자부터 왼쪽으로 검색해서 나온 첫 번째 L 위치 반환
  • 시작 위치가 음수이기 때문에 해당 위치부터 왼쪽으로 가면서 문자의 위치를 찾습니다.

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

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

REPLACE 함수는 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 때 사용합니다.

• 대체할 문자를 입력하지 않으면, 찾는 문자는 데이터에서 삭제됩니다.

REPLACE() 함수 사용 예시

데이터의 빈 공간을 특정 문자로 채우는 LPAD(Left Padding), RPAD(Right Padding) 함수

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

LPAD, RPAD 함수는 데이터와 자릿수를 지정하여 데이터의 길이가 자릿수보다 작을 경우 나머지 공간을 특정 문자로 채우고자 할 때 사용합니다. 패딩 처리는 주로, 데이터의 일부만 노출해야 하는 개인정보를 출력할 때 사용합니다.

• 빈 공간을 채울 문자를 지정하지 않으면 공백 문자가 들어갑니다.

LPAD(), RPAD() 함수 사용 예시

• LPAD의 경우 문자열 앞의 빈 공간을 채우며, RPAD의 경우 문자열 끝의 빈 공간을 채웁니다.
• LPAD와 RPAD 모두 주어진 문자열 데이터 길이가 보여주고자 하는 데이터 자릿수보다 크다면, 문자열 데이터의 앞부터 계산하여 주어진 자릿수만큼 출력해줍니다.

두 문자열 데이터를 합치는 CONCAT 함수와 || 연산자

CONCAT([연결할 문자열 데이터 또는 열이름(필수)], [연결할 문자열 데이터 또는 열이름(필수)])

CONCAT 함수는 두 개의 문자열 데이터를 하나의 데이터로 연결해주는 역할을 합니다. CONCAT 함수 대신 || 연산자를 사용해서 문자열을 연결할 수도 있습니다.

CONCAT 함수 사용 예시(왼쪽), 연산자 사용 예시(오른쪽)

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

TRIM, LTRIM, RTRIM 함수는 문자열 데이터 내에서 특정 문자를 지우기 위해 사용합니다. 이때, 삭제하고자 하는 문자는 문자열의 앞(왼쪽) 또는 뒤(오른쪽)에 존재하여야 삭제가 가능합니다.

보통 실무에서는 검색 기준이 되는 데이터의 양쪽 끝에 혹여 존재할 공백을 제거하고자 할 때 TRIM 함수를 사용합니다.

TRIM 함수

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

• 삭제할 문자를 지정하지 않을 경우 공백을 제거합니다.
• 삭제 옵션의 크게 3가지로 나뉩니다.
  • LEADING: 문자열 왼쪽에 있는 글자가 삭제할 문자와 동일할 경우 지움
  • TRAILING: 문자열 오른쪽에 있는 글자가 삭제할 문자와 동일할 경우 지움
  • BOTH: 문자열 양쪽의 글자가 삭제할 문자와 동일할 경우 모두 지움

TRIM() 함수 사용 예시

• TRIM 함수 예시의 A는 삭제 옵션을 따로 넣지 않아 default로 BOTH가 들어갔기 때문에 D와 결과가 동일합니다.
• E의 경우, 뒤쪽의 _가 지워지지 않은 이유는 문자열 마지막 위치가 공백이어서 _와 동일하지 않아 지워지지 않은 것입니다.

LTRIM, RTRIM 함수

LTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택) - default ' '])
RTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택) - default ' '])

LTRIM, RTRIM 함수는 각각 왼쪽, 오른쪽의 지정 문자를 삭제하는 데 사용합니다. TRIM 함수는 삭제할 문자로 하나만 작성할 수 있는 반면에 LTRIM과 RTRIM은 여러 종류의 문자를 삭제할 문자로 지정할 수 있습니다.

LTRIM(), RTRIM() 함수 사용 예시

• D와 E의 경우, 삭제할 문자를 두 개 이상 나열하여서 LTRIM은 문자열 처음부터, RTRIM은 문자열 끝에서부터 체크하여 문자열의 값이 삭제할 대상이 아닐 때까지 삭제합니다.

참고 자료

• 오라클로 배우는 데이터베이스 입문(이지훈 저)