[Oracle] 13. 단일 행 함수 : 문자 함수 정리

단일 행 함수의 종류

• 문자 함수
• 숫자 함수
• 날짜 함수
• 변환 함수
• 일반 함수

문자 함수

대소문자 변환 함수

함수명	기능
lower	문자열을 소문자로 변환한다. lower(문자열) lower('ORACLE') -> oracle
upper	문자열을 대문자로 변환한다. upper(문자열) upper('orcle') -> ORACLE
initcap	첫 문자만 대문자로 변환하고 나머지는 소문자로 변환한다. initcap(문자열) initcap('orAcleE') -> Oracle

예제 1. erp 부서가 있는 지역을 검색한다.

select loc erp_부서_지역

from dept

where lower(dname)='erp';

예제 2. 'XX 부서는 XX에 위치합니다.' 형식으로 부서 정보를 검색한다.

select initcap(dname) || ' 부서는 ' || loc || '에 위치합니다.' 부서_위치

from dept;

문자 연산 함수

함수명	기능
concat	두개의 문자열을 연결한다. (연결 연산자와 비슷한 기능을 수행한다.) concat(문자열1, 문자열2) concat('문','시현') -> 문시현
substr	문자열 내에 지정된 위치의 문자열을 반환한다. substr(문자열, 시작위치, [출력문자의 개수]) substr('oracle', 1, 2) -> or
length (lengthb)	문자열의 길이나 byte를 반환한다. length(문자열) lengthb(문자열) length('오라클') -> 3 lengthb('오라클') -> 6
instr	지정된 문자의 위치를 반환한다. instr(문자열, 검색문자, [시작위치, [횟수]] instr('oracle', 'a') -> 3
trim	접두어나 접미어를 잘라낸다. trim([leading | trailing | both] 제외 문자 from 문자열) 제외문자를 지정하지 않으면 공백문자를 잘라낸다. trim('o', from 'oracle') -> racle
lpad, rpad	지정된 문자열의 길이만큼 빈 부분에 문자를 채운다. lpad(문자열, 출력폭, 채움문자) lpad('2000', '10', '*') -> *****20000

예제 3. 부서의 명과 위치를 하나의 컬럼으로 검색

select concat(dname,' ' || loc) concat from dept;

예제 4. 부서명과 문자열의 길이를 출력

select dname, length(dname), length(dname) from dept;

예제 5. substr 함수를 이용해서 컬럼에 일부 내용만을 검색한다.

select ename, substr(ename,2), substr(ename, -2), substr(ename,1,2), substr(ename,-2,2)

from emp;

예제 6. 다양한 방법으로 'a'가 나오는 위치를 출력한다.

select instr('database','a'), instr('database','a',3), instr('database','a',1,3)

from dual;

예제  7. trim 함수를 이용 다양한 방법으로 문자열을 검색한다.

select trim('남' from '남기남'), trim(leading '남' from '남기남'), trim(trailing '남' from '남기남'), trim(' 남기남 ')

from dual;

예제 8. 이름과 급여를 각각 10 컬럼으로 검색한다.

select rpad(ename, 10, '*'), lpad(sal, 10, '*') from emp;

예제 9. 부서명의 마지막 글자를 제외하고 검색한다.

select dname, substr(dname, 1, length(dname) -1) dname

from dept;

함수명	기능
translate	문자 단위 치환된 값을 반환한다. translate(문자열, 검색문자, 치환문자) translate('oracle','a','#') -> or#cle
replace	문자열 단위 치환된 값을 반환한다. replace(문자열, 검색문자열, 치환문자열) replace('oracle','or','##') -> ##acle

예제 10. 형식이 비슷한 translate 와 replace를 동일한 치환을 통해 비교해 보자

select translate('World of Warcraft', 'Wo', '--') translate,

replace('World of Warcraft', 'Wo', '--') replace

from dual;

실습 ( 모두 단일 행 함수를 이용한다.)

 1. 이름이 두 글자인 학생의 이름을 검색한다.

select sname

from student where 2 = length(sname);

#length는 sname 컬럼에서 글자수를 세준다.

 2. '강'씨 성을 가진 학생의 이름을 검색한다.

select sname

from student

where '강' = substr(sname,0,1);

# substr은 sname 컬럼에서 첫번째자리인 0부터 1개의 글자를 출력해준다.

 3. 교수의 지위를 한글자로 검색한다. (ex. 조교수 ->조)

select pname, substr(orders,0,1)

from professor

where orders = '조교수';

# substr을 통해 조만을 출력한다.

 4. 일반 과목을 기초 과목으로 변경해서 모든 과목을 검색한다. (ex. 일반화학 -> 기초화학)

select replace(cname,'일반','기초')

from course

where cname like '%일반%';

# replace를 쓰면 해당 글자를 원하는 글자로 바꾸어 출력 가능하다.

 5. 만일 입력 실수로 student 테이블의 sname 컬럼에 데이터가 입력될 때 문자열 마지막에 공백이 추가 되었다면 검색할 때 이를 제외하고 검색하는 select문을 작성한다.

select trim(sname)

from student;

 6. 직원의 년봉을 10자리로 검색한다. 단 공백은 임의의 채움 문자로 채워 넣는다.

select ename, lpad((sal*12+nvl(comm,0)),10,'*')

from emp;

# 공백 문자를 없애는 nvl ( 보너스가 널값일수도 잇다.)

# 연봉을 만들기 위해 월급을 12번 곱하고 보너스를 더한다.

# lpad 는 정해진 글자만큼 출력하고 공백일 경우 *로 치환

 7. 학생의 이름을 검색한다. 단 성이 '심'인 학생은 성을 '사마'로 바꾸어 검색한다.

select replace(sname,'심','사마')

from student

where '심' = substr(sname,0,1);