Oracle 문자 함수의 활용: 대소문자 변환과 문자 조작 기능 소개

문자함수는 인수로 문자 데이터를 받아, 결과로 문자 또는 수치를 반환합니다. 문자함수는 ‘알파벳 대문자, 소문자 변환 함수’와 ‘문자 조작 함수’의 두 가지 분류가 있습니다.

이 글의 예시는 아래 글의 초기 데이터를 사용했습니다.

Oracle 일반 데이터베이스 인스턴스에 연습 데이터용 샘플 스키마 "SCOTT" 작성하기

"SCOTT" 스키마가 디폴트로 설치된 마지막 오라클 버전은 11g(11.2.0.x) 입니다. 11g 버전 이후 Oracle 12c(12.1.0.2) 버전부터 "SCOTT" 스키마는 기본적으로 설치되지 않도록 변경되...

대문자・소문자 변환 함수
문자 조작 함수

대문자・소문자 변환 함수

알파벳을 대문자로 변환해서 표시하거나 반대로 소문자를 변환해서 표시하는 등, 알파벳 데이터의 형태를 변환하는 함수입니다. UPPER, LOWER 및 INITCAP 함수가 있습니다.

UPPER・LOWER・INITCAP 함수

UPPER 함수

인수에 지정된 값을 대문자로 변환하는 함수입니다. 반환값의 타입은 문자형입니다.

UPPER('문자 데이터')

LOWER 함수

인수에 지정된 값을 소문자로 변환하는 함수입니다. 반환값의 타입은 문자형입니다.

LOWER('문자 데이터')

INITCAP 함수

인수에 지정된 각 단어 단위로 첫번째 문자를 대문자로 변환하고 두번째 문자 이후의 문자는 소문자로 변환합니다. 반환값의 타입은 문자형입니다.

INITCAP('문자 데이터')

예시

job 컬럼의 데이터를 대문자, 소문자로, ename 컬럼의 데이터를 첫 문자만 대문자로 출력하는 SQL문입니다.

SELECT UPPER(job), LOWER(job), INITCAP(ename) FROM emp
WHERE empno > 7800;

empno가 7800보다 큰 데이터의 대문자인 job과 소문자인 job, 첫 문자만 대문자인 ename가 출력된 것을 확인할 수 있습니다.

SQL> SELECT UPPER(job), LOWER(job), INITCAP(ename) FROM emp
  2  WHERE empno > 7800;

UPPER(JOB LOWER(JOB INITCAP(EN
--------- --------- ----------
PRESIDENT president King
SALESMAN  salesman  Turner
CLERK     clerk     James
ANALYST   analyst   Ford
CLERK     clerk     Miller

데이터가 어떤 식으로 되어있는지 모를 경우 WHERE구에서 대문자, 소문자 변환함수를 사용하면 검색이 쉬워집니다.
emp테이블에서 job가 CLERK인 데이터의 ename, job을 출력하는 SQL문입니다. job 컬럼에는 대문자인 데이터 뿐이지만 그것을 모른다는 가정 하에 LOWER 함수를 이용하여 결과값을 출력해보겠습니다.

SELECT ename, job FROM emp
WHERE LOWER(job) = 'clerk';

그냥 job으로 검색했을 때는 결과가 출력되지 않지만 job을 LOWER 함수로 감싸자 결과가 출력되는 것을 확인할 수 있습니다.

SQL> SELECT ename, job FROM emp
  2  WHERE job='clerk';

no rows selected

SQL> SELECT ename, job FROM emp
  2  WHERE LOWER(job) = 'clerk';

ENAME      JOB
---------- ---------
SMITH      CLERK
JAMES      CLERK
MILLER     CLERK

INITCAP 함수를 사용할 때의 주의점

INITCAP의 인수는 스페이스나 하이픈 등 영문, 숫자 이외의 기호 뒤의 문자도 한 단어의 선두로 간주하기 때문에 주의가 필요합니다.

‘test TEXT-duAL’라는 텍스트를 INITAP 함수로 감싸서 출력하는 SQL문입니다.

SELECT INITCAP('test TEXT-duAL') FROM dual;

‘test’가 Test로, ‘TEXT-duAL’이 ‘Text-Dual’로 변환되어 출력된 것을 확인할 수 있습니다.

SQL> SELECT INITCAP('test TEXT-duAL') FROM dual;

INITCAP('TESTT
--------------
Test Text-Dual

참조 : DUAL표

DUAL표는 모든 유저가 액세스 가능한 표로, DUMMY라는 1열에 1행(값은 X가 들어있다.)만 가지고 있는 표입니다. 더미표라고 불립니다.
DUAL표를 써서 검색하면 반드시 1행을 반환합니다.
표에 포함된 행을 사용하지 않고, 함수나 식의 결과만을 필요로 할 때 편리하게 활용할 수 있습니다.

SQL> SELECT * FROM dual;

D
-
X

문자 조작 함수

CONCAT 함수

CONCAT 함수는, 인수로 지정한 두개의 문자 데이터를 결합합니다. 연결연산자 ‘||’과 같은 결과를 만들지만, 결합연산자는 결합할 수 있는 문자 데이터의 개수에 제한이 없는 것에 비해서, CONCAT함수는 두 개의 데이터만 결합할 수 있습니다. 반환값의 타입은 문자형입니다.

CONCAT(문자 데이터1, 문자 데이터2)

아래 예시는 ‘Employee’라는 문자열과 ename 값을 결합하는 쿼리입니다.

SELECT CONCAT('Employee ', ename) as CONCAT_EX FROM EMP
WHERE job='MANAGER';

검색 조건과 일치하는 ename 값들이 문자열 ‘Employee’와 결합되어 출력됩니다.

CONCAT_EX        
------------------
Employee JONES     
Employee BLAKE     
Employee CLARK

SUBSTR 함수

SUBSTR 함수는 지정된 문자수의 문자를 추출합니다.

SUBSTR(문자 데이터, m, n)

문자 데이터 : 문자형의 열(column)명, 정수
m : 문자 위치를 나타내는 수치
n : 추출할 문자 길이를 나타내는 수치

m은 문자위치를, n은 추출할 문자 길이를 나타냅니다. 이 함수는 인수로 지정된 문자 데이터의 선두 m항목의 문자로부터 n문자의 길이의 문자 데이터를 추출합니다. n을 생략하면 m번째 문자부터 마지막 문자까지 추출합니다. m이 음수일 때는 문자 데이터의 말미부터 세어 m번째의 문자부터 n문자만큼을 추출합니다. 반환값의 타입은 문자형입니다.

‘System Technology-i’라는 문자 데이터로부터 특정 문자 데이터를 출력합니다. 아래의 쿼리는 여덟번째 위치의 문자부터 10글자를 출력하라는 내용입니다. 문자의 위치를 계산할 때는 공백도 포함됩니다.

SELECT SUBSTR('System Technology-i',8,10) as SUBTR_EX FROM dual;

SUBTR_EX 
----------
Technology

아래의 쿼리는 취득할 결과의 시작 위치만 지정하였습니다. 여덟번째 문자인 T부터 그 이후의 문자열이 출력됩니다.

SELECT SUBSTR('System Technology-i',8) as SUBSTR_EX FROM dual;

SUBSTR_EX  
------------
Technology-i

-8번째 문자부터 시작하는 문자열을 취득하는 쿼리입니다. 문자열의 가장 뒤에서부터 거꾸로 숫자를 세어가면 여덟번째는 n이 됩니다.

SELECT SUBSTR('System Technology-i',-8) as SUBSTR_EX FROM dual;

SUBSTR_E
--------
nology-i

LENGTH 함수

LENGTH 함수는 인수로 지정된 문자 데이터의 길이를 문자의 수로 표시합니다. (바이트 수가 아닙니다.) 이 함수는 공백이나 하이픈도 하나의 문자로 카운트합니다. 또 전각과 반각의 구별도 없습니다. 반환값의 타입은 문자형입니다.

LENGTH(문자 데이터)

문자 데이터에는 문자형의 컬럼명을 넣어도 되지만 아래의 쿼리처럼 고정 문자열을 넣을 수도 있습니다. System Technology-i는 공백을 포함하여 19글자이기 때문에 쿼리를 실행하면 19라는 결과가 나옵니다.

SELECT LENGTH('System Technology-i') as LENGTH_EX FROM dual;

 LENGTH_EX
----------
        19

INSTR 함수

INSTR 함수는 인수로 지정한 문자열의 출현위치를 수치로 반환합니다.

INSTR(문자 데이터, '문자열', m, n)

문자 데이터 : 문자열의 컬럼명, 정수 또는 상수
문자열 : 임의의 문자열
m : 문자의 검색 위치를 표시하는 수치
n : 문자의 출현 회수를 표시하는 수치

문자 데이터 m번째의 문자로부터 문자열을 검색하여, 문자열이 n회 째에 출현하는 위치를 반환합니다. m 및 n을 생략한 경우는 1입니다. 문자열이 검출되지 않았을 경우의 반환값은 0이 됩니다. 반환값의 타입은 수치형입니다.

검색 위치와 출현 회수를 생략한 쿼리입니다. 이 경우에는 문자데이터가 지정 문자열을 포함하는지를 확인하고, 가장 앞에 위치한 문자열의 위치를 반환합니다.

SELECT INSTR('System Technology-i', 'e') FROM dual;

         INSTR('SYSTEMTECHNOLOGY-I','E')
---------------------------------------
                                      5

‘System Technology-i’에는 ‘e’가 두 개 들어있습니다. 위의 쿼리에서는 앞의 ‘e’를 인식했지만 아래의 쿼리에서는 시작 위치를 지정해주었기 때문에 여섯번째 문자인 ‘m’ 이후에 배치된 ‘e’의 위치를 반환하게 됩니다.

SELECT INSTR('System Technology-i', 'e', 6, 1) FROM dual;

     INSTR('SYSTEMTECHNOLOGY-I','E',6,1)
---------------------------------------
                                      9

WHERE구에서 INSTR 함수를 사용한 검색조건을 지정한 SQL입니다. 문자 패턴을 사용한 LIKE 연산자와 같은 결과를 출력할 수 있습니다.

SELECT ename, job FROM emp
WHERE INSTR(job, 'M', 1, 1) = 1;

ENAME      JOB     
---------- ---------
JONES      MANAGER  
BLAKE      MANAGER  
CLARK      MANAGER

job 컬럼에 들어있는 데이터 중, 첫번째 글자가 ‘M’이며 1개만 들어있는 값으로써 ‘MANAGER’가 출력되었습니다.

LPAD, RPAD함수

인수로 지정된 문자열을 채우는 함수입니다.

LPAD 함수는, 인수로 지정된 문자데이터의 좌측에 채움 문자를 채워갑니다. 채움 문자를 생략한 경우에는 채움 문자로 공백을 사용합니다.

RPAD 함수는 인수로 지정된 문자 데이터의 우측에 채움 문자를 채워서(Padding) 전체 길이를 m문자만큼으로 만드는 함수입니다. 기본적인 개념은 LPAD 함수와 같습니다.

LPAD(문자 데이터, m, '채움 문자')
RPAD(문자 데이터, m, '채움 문자')

문자 데이터 : 문자형의 컬럼명, 정수 또는 상수
m : 전체 문자 길이를 나타내는 수치
채움 문자 : 임의의 문자열

전체 길이가 19자인 ‘System Technology-i’를 25자가 될 만큼 왼쪽, 즉 문자 데이터의 앞에 *를 추가하는 컬럼 LEFT와 25자가 될 만큼 오른쪽, 즉 문자 데이터의 뒤에 $를 추가하는 컬럼 RIGHT를 출력하는 쿼리입니다.

SELECT LPAD('System Technology-i',25,'*') as LEFT, RPAD('System Technology-i',25,'$') as RIGHT
FROM dual;

LEFT                                               RIGHT
-------------------------------------------------- --------------------------------------------------
******System Technology-i                          System Technology-i$$$$$$

이번에는 채움 문자를 지정하지 않은 쿼리입니다. 전체 길이가 25자가 될 만큼 문자 데이터의 왼쪽에 공백을 추가하고, 그것을 LENGTH 함수를 이용해 전체 길이를 출력하는 것으로 25자가 된 것을 정확히 알 수 있습니다.

SELECT LPAD('System Technology-i',25) as lpad, LENGTH(LPAD('System Technology-i',25)) as lengthlpad
FROM dual;

LPAD                                               LENGTHLPAD
-------------------------------------------------- ----------
      System Technology-i                                  25

REPLACE 함수

REPLACE 함수는 인수로 지정된 문자 데이터 중에서, 검색 문자열을 검출하면 치환 문자열로 치환합니다. 반환값의 타입은 문자형입니다.

REPLACE(문자 데이터, '검색 문자열', '치환 문자열')

문자 데이터 : 문자형의 컬럼명, 정수
검색 문자열 : 치환 전의 문자열
치환 문자열 : 치환 전의 문자열과 교체할 문자열

‘System Technology-i’의 ‘s’를 ‘5’로 교체하는 쿼리입니다. 대소문자를 구분하기 때문에 소문자 s인 두번째 s만 치환되었습니다.

SELECT REPLACE('System Technology-i', 's', '5') FROM dual;

REPLACE('SYSTEMTECH
-------------------
Sy5tem Technology-i

실제 테이블에 들어있는 컬럼을 치환한 쿼리입니다. SELECT문이기 때문에 실제 테이블에 존재하는 데이터가 치환되어 저장되지는 않습니다.

SELECT REPLACE(ENAME, 'S', 's') FROM EMP;

REPLACE(ENAME,'S','S
--------------------
sMITH
ALLEN
WARD
JONEs
MARTIN
BLAKE
CLARK
KING
TURNER
JAMEs
FORD
MILLER

12 행이 선택되었습니다.

TRIM 함수

TRIM 함수는 인수로 지정한 문자 데이터의 전후에 있는 버림 문자열을 삭제합니다. 반환값의 타입은 문자형입니다.

TRIM( [{LEADING | TRAILING | BOTH}] [버림 문자열 FROM] 문자 데이터)

LEADING : 선두에 있는 문자가 삭제 대상
TRAILING : 말미에 있는 문자가 삭제 대상
BOTH : 선두와 말미의 양방에 있는 문자가 삭제 대상
버림 문자열 : 버림 대상의 문자열 ※전각, 반각의 구별은 없다.
문자 데이터 : 문자형의 컬럼명, 정수 또는 상수

버림 문자열을 문자 데이터의 선행(Leading), 후속(Trailing), 선행과 후속 양방(Both)에서 검색합니다. 버림 문자열, 및 삭제 위치를 생략하면, 문자 데이터의 전후에 있는 반각 스페이스가 삭제됩니다.

문자 데이터 ‘AKASAKA’로부터 선행하는 ‘A’를 버립니다.

SELECT TRIM(LEADING 'A' FROM 'AKASAKA') FROM dual;

TRIM(L
------
KASAKA

문자 데이터 ‘AKASAKA’로부터 후속하는 ‘A’를 버립니다.

SELECT TRIM(TRAILING 'A' FROM 'AKASAKA') FROM dual;

TRIM(T
------
AKASAK

문자 데이터 ‘토마토마토’로부터 전후에 있는 ‘토’를 버립니다.

SELECT TRIM(BOTH '토' FROM '토마토마토') FROM dual;

TRIM(B
------
마토마

버림 문자열은 1바이트 문자, 2바이트 문자 모두 사용할 수 있지만 반드시 1문자여야 합니다.

SELECT TRIM(BOTH '토마' FROM '토마토마토') FROM dual
       *
1행에 오류:
ORA-30001: 트림 설정은 하나 문자만 가지고 있어야 합니다

SELECT TRIM(BOTH 'AK' FROM 'AKASAKA') FROM dual
       *
1행에 오류:
ORA-30001: 트림 설정은 하나 문자만 가지고 있어야 합니다