[SQL] 오라클 내장 함수 - 문자 함수 ( 단일행 함수 )

1. 문자 함수란

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

 

2. 대소문자를 바꿔주는 함수

- UPPER(문자열) : 괄호 안 문자열을 모두 대문자로 변환하여 반환

- LOWER(문자열) : 괄호 안 문자열을 모두 소문자로 변환하여 반환

- INITCAP(문자열) : 괄호 안 문자열을 첫 글자는 대문자, 나머지 문자는 소문자로 변환하여 반환

UPPER([문자열])
LOWER([문자열])
INITCAP([문자열])

-- 1.
SELECT 	ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM 	EMP;

-- 2. ENAME 이 scott 인 사원 조회하기
SELECT * FROM EMP
WHERE UPPER(ENAME) = UPPER('scott');

-- 3. ENAME 에 scott 이 포함된 사원 조회하기
SELECT * FROM EMP
WHERE UPPER(ENAME) LIKE UPPER('%scott%');

  (2)(3) : 찾으려는 문자열 데이터는 'scott'으로 명시했지만 양쪽 모두 UPPER 함수를 통해 대문자로 변환하여 비교했기 때문에 대소문자 상관없이 scott 데이터가 출력됨

 

3. 문자열 길이를 구하는 함수

- LENGTH(문자열) : 괄호 안 문자열의 길이 반환

- LENGTHB(문자열) : 괄호 안 문자열의 바이트 수 반환

LENGTH([문자열])
LENGTHB([문자열])

-- 1. 선택한 칼럼의 문자열 길이 구하기
SELECT 	ENAME, LENGTH(ENAME)
FROM 	EMP;

-- 2. ENAME 의 길이가 5 이상인 칼럼 조회하기
SELECT	ENAME, LENGTH(ENAME)
FROM 	EMP
WHERE 	LENGTH(ENAME) >= 5;

-- 3. LENGTH 와 LENGTHB 비교
SELECT 	LENGTH('한글'), LENGTHB('한글')
FROM 	DUAL;

  (3) LENGTH('한글') 은 2, LENGTHB('한글') 은 4가 출력된다. 한글은 한 문자당 2byte로 처리되기 때문

+ DUAL 테이블 이란? : 오라클의 최고 권한 계정인 SYS 소유의 더미 dummy 테이블. 데이터 저장 공간이 아닌 임시 연산이나 함수의 결과 값 확인 용도로 사용됨

 

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

- SUBSTR(문자열, 시작 위치, 추출 길이) : 시작 위치부터 추출 길이만큼 추출하여 반환 

- SUBSTR(문자열, 시작 위치) : 시작 위치부터 문자열 끝까지 추출하여 반환

- 시작 위치 n이 음수일 경우 오른쪽에서 n번째 위치에서 시작해 추출 길이 / 끝까지 추출

SUBSTR([문자열], [시작 위치], [추출 길이])
SUBSTR([문자열], [시작 위치])

-- 			 1. 				2.				   3.
SELECT 	JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5)
FROM	EMP;

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

JOB = SALESMAN
  (1) SUBSTR(JOB, 1, 2) : SA                         

  (2) SUBSTR(JOB, 3, 2) : LE                    

  (3) SUBSTR(JOB, 5) : SMAN
  (4) SUBSTR(JOB, -LENGTH(JOB)) : SALESMAN          

  (5) SUBSTR(JOB, -LENGTH(JOB), 2) : SA                   

  (6) SUBSTR(JOB, -3) : MAN

 

4. 문자열 안에서 특정 문자 위치를 찾는 함수

- INSTR(문자열, 특정 문자, 시작 위치, 몇번째) : 문자열 안에 특정 문자나 문자열이 어디에 포함되어 있는지 알고자 할때 사용

- 특정 문자가 문자열에 포함되어 있지으면 위치를 반환, 포함되어 있지 않다면 0을 반환

INSTR([대상 문자열],
	  [위치를 찾으려는 부분 문자/문자열],
      [위치 찾기를 시작할 대상 문자열 위치(선택, Default 1)],
      [찾으려는 문자가 몇 번째인지 지정(선택, Default 1)])
      
-- 1. 2. 3.
SELECT 	INSTR('HELLO, ORACLE!', 'L'),
       	INSTR('HELLO, ORACLE!', 'L', 5),
       	INSTR('HELLO, ORACLE!', 'L', 2, 2)
FROM	DUAL;

-- 4. LIKE와 비슷한 용도로 사용하기
SELECT	*
FROM	EMP
WHERE	INSTR(ENAME, 'S') > 0;

  (1) INSTR('HELLO, ORACLE!', 'L') : 3
  (2) INSTR('HELLO, ORACLE!', 'L', 5) : 12
  (3) INSTR('HELLO, ORACLE!', 'L', 2, 2) : 4

시작 위치 , 찾은 문자 ㅁ

  (4) INSTR(ENAME, 'S') > 0 = ENAME LIKE '%S%' : INSTR 함수의 결과값이 0보다 크다면 사원 이름에 S가 존재한다는 의미이기 때문에 LIKE 연산자를 사용한 것과 같은 효과를 볼 수 있음

 

5. 특정 문자를 다른 문자로 바꾸는 함수

- REPLACE(문자열 or 칼럼 이름, 찾는 문자, 대체할 문자) : 문자열 데이터에 포함된 특정 문자를 다른 문자로 대체하여 결과 반환

- 대체할 문자를 입력하지 않으면 찾는 문자를 문자열에서 삭제 후 반환

REPLACE([문자열 or 칼럼 이름], [찾는 문자], [대체할 문자(선택)])

-- 1. 2.
SELECT	REPLACE('010-1234-5678', '-', ' '),
		REPLACE('010-1234-5678', '-')
FROM    DUAL;

  (1) REPLACE('010-1234-5678', '-', ' ') : 010 1234 5678

  (2) REPLACE('010-1234-5678', '-') : 01012345678

 

6. 데이터의 빈 공간을 특정 문자로 채우는 함수

- 데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자리수보다 작은 경우 나머지 공간을 특정 문자로 채움

- LPAD(문자열 or 칼럼 이름, 자릿수, 채울 문자) : Left Padding. 왼쪽을 채울 문자로 채워 반환

- RPAD(문자열 or 칼럼 이름, 자릿수, 채울 문자) : Right Padding. 오른쪽을 채울 문자로 채워 반환

LPAD([문자열 or 칼럼 이름], [자릿수], [채울 문자(선택)])
RPAD([문자열 or 칼럼 이름], [자릿수], [채울 문자(선택)]) 

-- 1. 2. 3. 4.
SELECT 	LPAD('ORACLE', 10, '#'),
		RPAD('ORACLE', 10, '*'),
        LPAD('ORACLE', 10),
        RPAD('ORACLE', 10)
FROM    DUAL;

  (1) LPAD('ORACLE', 10, '#') : 왼쪽 남은 자리를 #로 채움

  (2) RPAD('ORACLE', 10, '*') : 오른쪽 남은 자리를 *로 채움

  (3) LPAD('ORACLE', 10) : 왼쪽 남은 자리를 공백으로 채움

  (4) RPAD('ORACLE', 10) : 오른쪽 남은 자리를 공백으로 채움

 

7. 두 문자열을 합치는 함수

- CONCAT(문자열 or 칼럼 이름, 문자열 or 칼럼 이름) : 두개의 문자열을 하나의 데이터로 연결 후 결과 반환

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

CONCAT([문자열 or 칼럼 이름], [문자열 or 칼럼 이름])

-- 1. 2. CONACT 함수
SELECT	CONCAT(EMPNO, ENAME),
		CONCAT(EMPNO, CONCAT(' : ', ENAME))
FROM	EMP
WHERE	EMPNO = 7788, ENAME = 'SCOTT';

-- 3. 4. || 연산자
SELECT	EMPNO || ENAME,
		EMPNO || ' : ' || ENAME
FROM	EMP
WHERE	EMPNO = 7788, ENAME = 'SCOTT';

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

  (3) EMPNO || ENAME : 7788SCOTT
  (4) EMPNO || ' : ' || ENAME : 7788 : SCOTT

 

8. 특정 문자를 지우는 함수

- 삭제할 문자를 지정하지 않으면 기분적으로 공백이 삭제됨

- TRIM(삭제 옵션(선택), 삭제할 문자(선택, default 공백) FROM 문자열)

- 옵션 : 왼쪽의 글자를 지우는 LEADING, 오른쪽의 글자를 지우는 TRAILING, 양쪽의 글자를 모두 지우는 BOTH

- LTRIM(문자열, 삭제할 문자 집합(선택, defalut 공백) / RTRIM(문자열, 삭제할 문자 집합(선택, defalut 공백)

- TRIM은 문자 하나만 지정, LTRIM, RTRIM은 여러 문자 지정이 가능

- 맨 왼쪽, 오른쪽부터 지우기 때문에 LTRIM('<_O<_racle_>', '_<') 의 경우 O<_racle 이 남게 됨

TRIM([삭제 옵션(선택)], [삭제할 문자(선택, default 공백) FROM 문자열])
LTRIM([문자열], [삭제할 문자 집합(선택, defalut 공백)])
RTRIM([문자열], [삭제할 문자 집합(선택, defalut 공백)])

-- 1. 2. 3. 4. 삭제할 문자가 없을 때 TRIM
SELECT 	'[' || TRIM(' _ _Oracle_ _ ') || ']',
		'[' || TRIM(LEADING FROM ' _ _Oracle_ _ ') || ']',
		'[' || TRIM(TRAILING FROM ' _ _Oracle_ _ ') || ']',
        '[' || TRIM(BOTH FROM ' _ _Oracle_ _ )' || ']'
FROM    DUAL;

-- 5. 6. 7. 8. 삭제할 문자가 있을 때 TRIM
SELECT 	'[' || TRIM('_' FROM '_ _Oracle_ _') || ']',
		'[' || TRIM(LEADING '_' FROM '_ _Oracle_ _') || ']',
		'[' || TRIM(TRAILING '_' FROM '_ _Oracle_ _') || ']',
        '[' || TRIM(BOTH '_' FROM '_ _Oracle_ _') || ']'
FROM    DUAL;

-- 9. 10. 11. 12. 13.
SELECT 	'[' || TRIM(' _Oracle_ ') || ']',
		'[' || LTRIM(' _Oracle_ ') || ']',
		'[' || LTRIM('<_Oracle_>', '<_') || ']',
        '[' || RTRIM(' _Oracle_ ') || ']',
		'[' || RTRIM('<_Oracle_>', '>_') || ']'
FROM    DUAL;

  (1) '[' || TRIM(' _ _Oracle_ _ ') || ']'                                : [ _ _Oracle_ _ ] -> [_ _Oracle_ _] 양쪽 공백이 삭제됨
  (2) '[' || TRIM(LEADING FROM ' _ _Oracle_ _ ') || ']'    : [ _ _Oracle_ _ ] -> [_ _Oracle_ _ ] 왼쪽 공백만 삭제됨
  (3) '[' || TRIM(TRAILING FROM ' _ _Oracle_ _ ') || ']'    : [ _ _Oracle_ _ ] -> [ _ _Oracle_ _] 오른쪽 공백만 삭제됨
  (4) '[' || TRIM(BOTH FROM ' _ _Oracle _ _ ') || ']'          : [ _ _Oracle_ _ ] -> [_ _Oracle_ _] 양쪽 공백이 삭제됨
  (5) '[' || TRIM('_' FROM '_ _Oracle_ _') || ']'                  : [_ _Oracle_ _] -> [ _Oracle_ ] 양쪽 _가 삭제됨
  (6) '[' || TRIM(LEADING '_' FROM '_ _Oracle_ _') || ']' : [_ _Oracle_ _] -> [ _Oracle_ _] 왼쪽 _만 삭제됨
  (7) '[' || TRIM(TRAILING '_' FROM '_ _Oracle_ _') || ']' : [_ _Oracle_ _] -> [_ _Oracle_ ] 오른쪽 _만 삭제됨

  (8) '[' || TRIM(BOTH '_' FROM '_ _Oracle_ _') || ']        : [_ _Oracle_ _] -> [ _Oracle_ ] 양쪽 _가 삭제됨

  (9) '[' || TRIM(' _Oracle_ ') || ']'                                      : [ _Oracle_ ] -> [_Oracle_] 양쪽 공백이 삭제됨
  (10) '[' || LTRIM(' _Oracle_ ') || ']'                                  : [ _Oracle_ ] -> [_Oracle_ ] 왼쪽 공백만 삭제됨
  (11) '[' || LTRIM('<_Oracle_>', '<_') || ']'                        : [<_Oracle_>] -> [Oracle_>] 왼쪽 <_가 삭제됨
  (12) '[' || RTRIM(' _Oracle_ ') || ']'                                  : [ _Oracle_ ] -> [ _Oracle_] 오른쪽 공백만 삭제됨
  (13) '[' || RTRIM('<_Oracle_>', '>_') || ']                         : [<_Oracle_>] -> [<_Oracle] 오른쪽 _>가 삭제됨