[Oracle] 함수의 종류와 문자함수

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

 

오라클 함수의 종류

• 내장 함수(built-in function) : 오라클에서 기본으로 제공하고 있는 함수
• 사용자 정의 함수(user-defined function) : 사용자가 필요에 의해 직접 정의한 함수

 

   내장함수의 종류 

• 단일행 함수(single-row function) : 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수
• 다중행 함수(multiple-row function) : 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수

 

 

 

 

문자 함수 (단일행 함수)

 

  ■ 대 · 소문자를 바꿔주는 함수(UPPER, LOWER, INITCAP)

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

   UPPER ,  LOWER ,  INITCAP 함수를 사용하려면 입력 데이터에 열 이름이나 데이터를 직접 지정해야 한다. 문자열을 비교할때 전부 대문자로 바꾼 뒤에 비교하거나 하는 식으로 활용한다.

 

 

 

  ■ 문자열 길이 (LENGTH, LENGTHB)

함  수	설  명
LENGTH(문자열 또는 열이름)	문자열의 길이를 반환
LENGTHB(문자열 또는 열이름)	문자열의 바이트(byte) 수를 반환 (영어는 한 글자에 1byte, 한글은 2byte)

 

SELECT ENAME, LENGTH(ENAME)
FROM EMP
WHERE LENGTH(ENAME) >= 5;

위의 코드처럼  LENGTH 함수를  WHERE 절에서 사용해서 행을 선별할 수 있다.

 

 

+ DUAL 테이블

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

   DUAL 테이블은 오라클의 최고 권한 관리자 계정인  SYS 소유의 테이블로  SCOTT 계정도 사용할 수 있는 더미(dummy)테이블이다. 데이터 저장공간이 아니라서 임시 연산이나 함수의 결과값 확인을 위한 용도로 사용된다. 

 

 

 

  ■ 문자열 일부 추출 (SUBSTR)

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

 

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

위의 코드처럼 다른 함수의 결과값을  SUBSTR 함수의 입력값으로 사용할 수도 있다. 

 

 

 

실행결과

 

 

 

  ■ 특정 문자 위치 찾기 (INSTR)

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

-- ex
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 함수를 사용한다. 총 4개의 입력 값을 지정할 수 있으며, 최소 원본 문자열 데이터와 찾으려는 문자 두 가지는 반드시 지정해 주어야 한다. 

 

실행결과

 INSTR_3 은 마지막 입력값이  2 이므로 두 번째로 등장한  L 을 찾은 것을 확인할 수 있다.

 

 

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

 INSTR 은 찾으려는 문자가 문자열 데이터에 포함되어 있지 않다면 위치 값이 없으므로  0 을 반환한다.

이를 이용해서  LIKE 와 비슷하게 사용할 수도 있다. 위의 코드는  INSTR 함수로 사원 이름에 문자  S 가 있는 행을 구하는 예제이다.

 

 

 

  ■ 다른 문자로 바꾸기 (REPLACE)

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

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

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

대체할 문자를 입력하지 않으면 지정한 문자는 삭제된다.

 

 

실행결과

 

 

 

  ■ 빈 공간을 특정 문자로 채우기 (LPAD, RPAD)

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

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

  각각 Left Padding, Right Padding을 뜻하며 데이터 길이가 지정된 자리수보다 작을 경우에 나머지 공간을 특정 문자로 채우는 함수이다. 채울 문자를 지정하지 않으면 빈 공간만큼 공백이 채워진다.

 

실행결과

 

 

 

  ■ 문자열 합치기 (CONCAT)

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

두 개의 열이나 문자열 데이터를 하나로 연결해 주는 함수이다.  CONCAT 을 사용한 결과 값은 다시 다른  CONCAT 함수의 입력 값으로 사용하는 것도 가능하다.

 

실행결과

 

 

 + || 연산자

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

 CONCAT 함수와 유사하게  || 연산자도 열이나 문자열을 연결한다.

 

 

 

  ■ 특정 문자 지우기 (TRIM, LTRIM, RTRIM)

  1. TRIM

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

-- ex1 (삭제할 문자를 지정하지 않는 경우)
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;


-- ex2 (삭제할 문자를 지정하는 경우)
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 은 문자열 데이터 내에서 특정 문자를 지우기 위해 사용한다. 삭제할 문자가 생략된 경우에는 기본적으로 공백을 제거하고 삭제 옵션에 따라  LEADING 은 왼쪽,  TRAILING 은 오른쪽,  BOTH 는 양쪽의 문자를 모두 지운다. 아무 옵션도 안쓸 경우  BOTH 와 같이 양쪽의 문자를 지운다.

 

ex1 실행결과

ex2 실행 결과

 

 

  2. LTRIM, RTRIM

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

-- ex
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;

   LTRIM 은 왼쪽,  RTRIM 은 오른쪽의 지정 문자를 삭제하는 데 사용된다.  TRIM 함수와 달리 삭제할 문자를 여러 개 지정할 수 있다는 점이 다르다.  TRIM 과 마찬가지로 삭제할 문자를 지정하지 않을 경우  LTRIM 은 왼쪽,  RTRIM 은 오른쪽의 공백을 제거한다.( TRIM 은 양쪽)

 

실행결과

 LTRIM 을 사용했을 때  <_<_Oracle 와 같이 지정한 문자가 연속으로 나오는 경우는 한꺼번에 제거되어 결과값이  Oracle 이 되지만,  <_O<_racle 과 같은 문자열은  O 에서 삭제 작업이 끝나기 때문에  O<_racle 이 된다.