2022-11-07 (4) 오라클 함수 - 문자 함수

[문자 함수]

[대소문자 변환 UPPER / LOWER / INITCAP]

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

언제 쓰냐? 예를들어 대소문자 구분없이 Scott을 찾는다고 치자.

단순히 where (column) LIKE '%Scott%'이라고 조건을 주게 되면 SCOTT, scott, ScOtT 등 대소문자가 혼합된 경우에는 검색 결과에 걸리지 않는다.

그러나 문자를 전부 대문자로 바꾸거나 소문자로 바꾸어 비교하게 된다면, 대소문자 여부와 상관없이 검색 단어와 일치하는 문자열을 포함한 데이터를 찾아낼 수 있을 것이다.

--이름이 scott인 사람 찾기
SELECT *
FROM EMP
WHERE UPPER(ENAME) = UPPER('scott');

--이름에 scott가 포함되는 경우 찾기
SELECT *
FROM EMP
WHERE UPPER(ENAME) LIKE UPPER('%scott%');

 

[문자열 길이 LENGTH]

: 특정 문자의 길이를 구할 때 사용한다.

--숫자 비교도 가능함
SELECT ENAME, LENGTH(ENAME)
FROM EMP
WHERE LENGTH(ENAME) >= 5;

- LENGTH / LENGTHB

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

LENGTH 는 문자열의 길이, LENGTHB는 문자열의 바이트 수를 반환한다. 한글은 한 문자당 2byte로 처리되기 때문에, 각각의 결과는 2 / 4 가 반환된다.

 

참고) DUAL 테이블은 임시 연산이나 함수의 결과 값 확인 용도로 종종 사용되는 더미 테이블이다.

 

[문자열 일부 추출 SUBSTR]

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

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

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

SUBSTR(JOB, 1, 2)의 의미

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

SUBSTR(JOB, -LENGTH(JOB))의 의미

SUBSTR(JOB, -LENGTH(JOB), 2)의 의미

 

SUBSTR(JOB, -3)의 의미

[특정 문자 위치 찾기 INSTR]

: 원본 문자열과 그 안에서 찾으려는 문자 두 개의 값은 반드시 지정해야 한다. 찾지 못하면 0을 반환한다.

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

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,--시작위치와 몇번째 L을 찾을 건지 지정X
	INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2,--다섯번째 글자 O부터 L을 찾음
    INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3 --두번째 글자 E부터 시작해서 두번째 L을 찾음
FROM DUAL;

[특정 문자 치환 REPLACE]

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

대체 문자를 입력하지 않으면 찾는 문자는 문자열에서 삭제처리된다.

SELECT '010-1234-5678' AS "치환전",
    REPLACE('010-1234-5678', '-', ' ') AS "공백처리",
    REPLACE('010-1234-5678', '-') AS "삭제"
FROM DUAL;

 

[데이터 빈공간 채우기 LPAD, RPAD]

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;

[문자 합치기 CONCAT]

: 다음 두가지 모두 결과는 동일하다.

SELECT CONCAT('ORACLE', 'STUDY'),
    CONCAT('ORACLE', CONCAT(':', 'STUDY'))
FROM DUAL;

SELECT 'ORACLE' || 'STUDY',
        'ORACLE' || ':' || 'STUDY'
FROM DUAL;

[특정 문자 지우기 TRIM, LTRIM, RTRIM]

: 삭제할 문자가 생략될 경우에 기본적으로 공백을 제거한다. LEADING은 왼쪽, TRAILING은 오른쪽, BOTH는 양쪽의 글자를 모두 지운다. 예시만 참고하자.

 

- 삭제할 문자가 없을 때

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;

- 삭제할 문자가 없을 때

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;