11강 오라클 SQL 문자함수

SQL 주요 함수(문자함수)

*  이 장에서 다룰 내용

1.문자 처리 함수

1. 문자 처리 함수

구분	설명
LOWER	소문자로 변환한다.
UPPER	대문자로 변환한다.
INITCAP	첫 글자만 대문자로 나머지 글자는 소문자로 변환한다.
CONCAT	문자의 값을 연결한다.
SUBSTR	문자를 잘라 추출한다. (한글 1Byte)
SUBSTRB	문자를 잘라 추출한다. (한글 2Byte)
LENGTH	문자의 길이를 반환한다. (한글 1Byte)
LENGTHB	문자의 길이를 반환한다. (한글 2Byte)
LPAD, RPAD	입력 받은 문자열과 기호를 정렬하여 특정 길이의 문자열로 반환한다.
TRIM	잘라내고 남은 문자를 표시한다.
CONVERT	CHAR SET을 변환한다.
CHR	ASCII 코드 값으로 변환한다.
ASCII	ASCII 코드 값을 문자로 변환한다.
REPLACE	문자열에서 특정 문자를 변경한다.

 

1-1. 대문자로 변환하는 UPPER 함수

■ UPPER 함수는 입력한 문자값을 대문자로 변환하는 함수입니다.

■ 문자가 변환되는 결과를 명료하게 살펴보기 위해서 특정 테이블에 대한 컬럼에 대해서 함수를 적용하는 것은 나중에 하기로 하고 우선 문자 상수에 대해서 적용해보도록 합시다.

예	SELECT 'Welcome to Oracle', UPPER('Welcome to Oracle') FROM DUAL;

UPPER 함수

1-2. 소문자로 변환하는 LOWER 함수

■ LOWER 함수는 문자열을 모두 소문자로 변경합니다.

예	SELECT 'Welcome to Oracle', LOWER('Welcome to Oracle') FROM DUAL;

LOWER 함수

1-3. 이니셜만 대문자로 변환하는 INITCAP 함수

■ INITCAP 함수는 문자열의 이니셜만 대문자로 변경합니다.

예	SELECT 'WELCOME TO ORACLE',                INITCAP('WELCOME TO ORACLE') FROM DUAL;

INITCAP 함수

*. 탄탄히 다지기.

탄탄히 다지기(1)

@ ''안에 들어가 있으면 대소문자 구별함.

탄탄히 다지기(2)

select empno, ename, job from emp where lower(job) = 'manager';

 

1-4. 문자 길이를 구하는 LENGTH

■ LENGTH 함수는 컬럼에 저장된 데이터 값이 몇 개의 문자로 구성되었는지 길이를 알려주는 함수입니다. 영문자와 한글의 길이를 구해봅시다.

예	SELECT LENGTH('Oracle'), LENGTH('오라클') FROM DUAL;

LENGTH 함수

1-5. 바이트 수를 알려주는 LENGTHB 함수

■ 이번에 살펴볼 LENGTHB 함수는 바이트 수를 알려주는 함수입니다. LENGTH 함수와 어떤 차이가 있는지 살펴봅시다.

예	SELECT LENGTH('Oracle'), LENGTH('오라클') FROM DUAL;

LENGTHB 함수

■ 한글 1자는 2바이트를 차지합니다. 그렇기 때문에 수행 결과를 보면 한글 3자로 구성된 '오라클'의 LENGTHB함수의 결과는 6이 됩니다.

 

1-6. 문자열 일부만 추출하는 SUBSTR 함수

■ SUBSTR과 SUBSTRB 함수는 대상 문자열이나 칼럼의 자료에서 시작위치부터 선택 개수만큼의 문자를 추출합니다. SUBSTRB 함수도 같은 형식이지만 명시된 개수만큼의 문자가 아닌 바이트 수를 잘라낸다는 점에서만 차이가 나타납니다.

형식	SUBSTR(대상, 시작위치, 추출할 개수)
예	SELECT SUBSTR('Welcome to Oracle', 4, 3) FROM DUAL;

SUBSTR 함수

select substr('Welcome to oracle', 4, 3) from dual;

-- com (4번째 자리부터 3개)

select substr('Welcome to oracle', 3, 4) from dual;

-- lcom (3번째 자리부터 4개)

 

■ 시작위치 인자 값을 음수 값으로 줄 수 있는데 이때는 문자열의 앞쪽이 아닌 뒤 쪽에서부터 세어서 시작위치를 잡습니다.

예	SELECT SUBSTR('Welcome to Oracle', -4, 3) FROM DUAL;

시작위치 인자 값을 음수 값으로

select substr('Welcome to oracle', -3, 2) from dual;

-- cl(뒤에서부터 3번째 자리부터의 2글자)

 

■ 사원들의 입사년도만 출력하려면 어떻게 해야 할까요?

■ SUBSTR 함수를 이용해서 입사일을 저장하고 있는 HIREDATE 컬럼에서 첫 글자부터 2개를 추출하면 됩니다.

■ 입사한 달만 출력하려면 HIREDATE 컬럼에서 네번째 글자부터 2개를 추출하면 됩니다.

예	SELECT SUBSTR(HIREDATE, 1, 2) 년도, SUBSTR(HIREDATE, 4, 2) 달 FROM DUAL;

입사일 계산하기.

80/12/17 -- 첫 글자부터 시작해서 2개

select substr(hiredate, 1, 2) from emp; -- 년도

80/12/17 -- 4번째 글자부터 시작해서 2개

select substr(hiredate, 4, 2) from emp; -- 달

80/12/17 -- 7번째 글자부터 시작해서 2개

select substr(hiredate, 7, 2) from emp; -- 일

 

■ 9월에 입사한 사원을 출력해보시오.

예	SELECT * FROM EMP WHERE SUBSTR(HIREDATE, 4, 2) = '09';

SUBSTR 조건식

*. 탄탄히 다지기.

탄탄히 다지기(3)

2. select hiredate from emp where substr(hiredate, 1, 2) = '87';

3. select ename from emp where substr(ename, -1, 1) = 'E';

 

1-7. 바이트 수를 기준으로 문자열 일부만 추출하는 SUBSTRB 함수

■ SUBSTRB 함수는 문자열을 추출하기 위해서 문자를 셀 때 문자의 개수가 아닌 그 문자가 메모리에 저장되는 바이트 수로 셉니다.

■ 영문자의 1자는 메모리에 1바이트로 저장되기 때문에 SUBSTR함수와 SUBSTRB 함수 어떤 함수를 사용하여도 결과가 동일합니다.

예	SELECT SUBSTR('Welcome To Oralce', 3, 4),                SUBSTRB('Welcome To Oracle', 3, 4) FROM DUAL;

 

■ 한글 1자는 2바이트를 차지하기 때문에 SUBSTR 함수와 SUBSTRB 함수의 결과가 달라집니다.

예	SELECT SUBSTR('월컴투오라클', 3, 4),                SUBSTRB('월컴투오라클', 3, 4) FROM DUAL;

SUBSTRB 차이

SELECT SUBSTR('월컴투오라클', 3, 4),
               SUBSTRB('월컴투오라클', 3, 4)
FROM DUAL;

실제결과? 뭐지

1-8. 특정 문자의 위치를 구하는 INSTR 함수

■ INSTR  함수는 대상 문자열이나 칼럼에서 특정 문자가 나타나는 위치를 알려줍니다.

■ 문자열 'Welcome to Oracle'에 'O'가 저장된 위치가 얼마인지 알고 싶을 때에는 INSTR 함수를 사용하여 다음과 같이 쿼리문을 작성할 수 있습니다.

예	SELECT INSTR('WELCOME TO ORACLE', 'O') FROM DUAL;

INSTR 함수

select instr('korea', 'r') from dual;

실습

■  INSTR 함수의 기본 형식은 다음과 같습니다.

형식	INSTR(대상, 찾을글자, 시작위치, 몇_번째_발견)

■ 구문에서 보듯이 앞선 예제에서는 '시작위치'와 '몇 번째 발견'을 생략한 채 사용한 것으로 이들 값을 생략하면 모두 1로 간주되므로 시작 위치도 1이고 첫 번째 발견될 위치를 반환합니다.

예	SELECT  INSTR('WELCOME TO ORACLE', 'O', 6, 2) FROM DUAL; --                                   6번째 자리에서 2번째 발견된 O의 위치.

INSTR 함수

1-9. 바이트 수를 기준으로 문자의 위치를 구하는 INSTRB 함수

■ INSTRB 함수 역시 SUBSTRB 함수에서와 마찬가지로 문자의 위치를 알아내기 위한 기준으로 바이트합니다.

■ 영문자는 1글자가 1바이트이므로 INSTR 함수와 INSTRB 함수의 결과가 동일합니다.

■ 그러므로 1글자가 2바이트인 한글 상수로 예를 들어보도록 합시다.

예	SELECT  INSTR('데이터베이스', '이', 3, 1)                 INSTRB('데이터베이스', '이', 3, 1) FROM DUAL;

INSTRB 함수

@ 현재 오라클에서 INSTRB 사용안됨.

 

*. 탄탄히 다지기

탄탄히 다지기(4)

select ename from emp where substr(ename, 3, 1) = 'R';

select ename from emp where ename like '__R%';

 

1-10. 특정 기호로 채우는 LPAD/RPAD 함수

■ LPAD(LEFT PADDING) 함수는 칼럼이나 대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채웁니다.

예	SELECT LPAD('Oracle', 20, '#')                                     -- 20자리중 앞자리 14개에 #을 채우고 나머지 6자리에 oracle  FROM DUAL;

LPAD 함수

■ RPAD(RIGHT PADDING) 함수는 반대로 칼럼이나 대상 문자열을 명시된 자릿수에서 왼쪽에 나타내고, 남은 오른쪽 자리를 특정 기호로 채웁니다.

RPAD 함수

■ LTRIM 함수는 문자열의 왼쪽(앞)의 공백 문자들을 삭제합니다.

예	SELECT LTRIM('        Oracle   ')                                    -- 오른쪽은 제거 안함. FROM DUAL;

LTRIM 함수

■ RTRIM 함수 역시 다음과 같이 기술하면 오른쪽 공백 문자를 잘라냅니다.

예	SELECT RTRIM('        Oracle   ')                                    -- 왼쪽공백은 제거 안함. FROM DUAL;

■  TRIM 함수는 칼럼이나 대상 문자열에서 특정 문자가 첫 번째 글자이거나 마지막 글자이면 잘라내고 남은 문자열만 반환합니다.

예	SELECT TRIM('     korea    ')                                    -- 왼쪽, 오른쪽 공백 다 제거해줌. FROM DUAL;

학습정리.

구분	설명
LOWER	소문자로 변환한다.
UPPER	대문자로 변환한다.
INITCAP	첫 글자만 대문자로 나머지 글자는 소문자로 변환한다.
CONCAT	문자의 값을 연결한다.
SUBSTR	문자를 잘라 추출한다. (한글 1Byte)
SUBSTRB	문자를 잘라 추출한다. (한글 2Byte)
LENGTH	문자의 길이를 반환한다. (한글 1Byte)
LENGTHB	문자의 길이를 반환한다. (한글 2Byte)
LPAD, RPAD	입력 받은 문자열과 기호를 정렬하여 특정 길이의 문자열로 반환한다.
TRIM	잘라내고 남은 문자를 표시한다.
CONVERT	CHAR SET을 변환한다.
CHR	ASCII 코드 값으로 변환한다.
ASCII	ASCII 코드 값을 문자로 변환한다.
REPLACE	문자열에서 특정 문자를 변경한다.

* CONCAT 사용예제

select concat ( ename, ' is ' || job ) from emp;

CONCAT 함수

* REPLACE사용예제

select replace( 'abcdefg', 'c', 'ccc') from dual;

REPLACE 사용예제