문자함수

• lower : 소문자로 변환
• upper : 대문자로 변환

select lower(name) from customer;
select upper(name) from customer;

select count(name) from customer
where UPPER(name) = 'TIGER';

 

• length : 문자열 길이
• ltrim : 왼쪽 공백을 제거
• rtrim : 오른쪽 공백을 제거
• trim : 양 옆의 공백을 제거

select length('hello') from dual; -- 5
select length('  hello') from dual; -- 7
select length(trim('  hello')) from dual; -- 5
select length('   hello    ') from dual; -- 12
select length(trim('   hello    ')) from dual; -- 5

insert into customer values(9, '    tiger', null, null);
insert into customer values(10, '    tiger    ', null, null);
insert into customer values(11, 'TIGER   ', null, null);

select count(*) from CUSTOMER

where trim(upper(name))= 'TIGER';

select * from book
where length(bookname) >= 7;

• lengthb : 문자열 바이트 값

select lengthb('홍') from dual; -- 3

 

• replace : 문자열 변경

select replace(데이터, '원래 글자','신나는') from dual;
select replace('재미있는 자바', '재미있는', '신나는') from dual;
select bookid, replace(bookname, '야구', '농구') bookname, publisher, price from book;

 

• substr : 문자열의 일부분을 잘라주는 함수(단, 인덱스가 1부터 시작한다)

substr(문자열, 시작위치, 개수)

select substr('hello', 1,1) from dual;
select substr('hello', 1,2) from dual;
select substr('hello', 2,2) from dual;

--연습 : 실습을 위하여 emp에 컬럼을 추가합니다.
alter table emp add jumin varchar2(14);
alter table emp add email varchar2(50);
select * from emp;


update emp set jumin = 9010101234567, email = 'edj19877@gmail.com' where ename = '이동준';
update emp set jumin='980728-1234567', email = 'goodstar7369@naver.com'  where ename = '박성빈';
update emp set jumin ='021010-4123456', email = 'dladbsk674@naver.com' where eno = 1003;
update emp set jumin = '920511-1234566', email = 'zxzz8014@naver.com' where eno = 1006;
update emp set email='principle950@naver.com', jumin='950830-2000000' where ename = '최모래';
update emp set email='uhyeonjin0619@gmail.com', jumin='960619-1234567' where ename='유현진';
update emp set jumin='941104-1234567', email='pjsu94@naver.com' where eno = 1009;
update emp set jumin = '991013-2222222', email = '700eeu@gmail.com' where ename = '김예은';
update emp set jumin = '971223-2000000', email = 'qordpsem@gmail.com' where eno = 1008;
update emp set jumin = '970217-1111111', email = 'ghdtjrdud38@naver.conm' where ename = '홍석영';
update emp set jumin='970128-1111111', email='dlwodnjs0128@naver.com' where eno=1011;
update emp set jumin = '930304-1023412' , email = 'whdudgms123@naver.com' where ename= '조영흔';
UPDATE emp SET jumin = '980105-1212331', email = '85356271a@gmail.com' WHERE eno = 1014;
UPDATE EMP SET JUMIN = '010205-3123456', EMAIL = 'rhdgmltkd88@gmail.com' WHERE ENO = 1015;

-- 개발팀에 근무하는 남성 직원들에게 특별 상여금을 지급하려고 한다
-- 상여금은 연봉의 150%이다.
-- 대상자의 사원번호, 이름, 상여금, 관리자명, 주민번호를 출력하라.
select e.eno, e.ename, 12*(e.salary + e.comm)*1.5 상여금, m.ename, e.jumin
from emp e, emp m
where e.mgr = m.eno and
substr(e.jumin,8,1) = '1';

-- 서브쿼리로 작성
-- 개발팀에 근무하는 사원번호
select eno from emp where dno in (
    select dno from dept where dname like '개발%'
);

-- 개발팀에 근무하는 남자 사원 번호 
select eno from emp where dno in (
    select dno from dept where dname like '개발%'
) and substr(jumin, 8, 1) in (1, 3);

-- 개발 팀에 근무하는 남자 사원 번호,  이름, 상여금, 주민번호, ename, 
select eno, ename, ((salary + comm)*12)* 1.5 상여금,(
    select ename from emp m where e.mgr = m.eno
) 관리자명, jumin FROM emp e
where dno in (
    select dno from dept where dname like '개발%'
);

 

• instr : 해당 문자(열) 이 있는 맨 처음 나오는 위치를 알려준다.

select 'hello' from dual;
select instr('hello', 'l') from dual; -- 3
select instr('hello', 'k') from dual; -- 0

--연습 모든 직원의 사원번호, 주민번호, 아이디를 출력한다.
--주민번호는 생년월일과 성별까지만 출력한다.
select eno, substr(jumin, 1, instr(jumin, '-')+1), substr(email, 1,instr(email,'@') -1) from emp;

• lpad / rpad
  • 왼쪽/오른쪽의 공백을 특정 문자로 채움
  • 문자열을 자리수만큼 잡아서 출력하되 왼쪽/오른쪽 빈 칸을 채울 문자로 채운다.

lpad(문자열, 자리수, 채울 문자)
rpad(문자열, 자리수, 채울 문자)

select lpad('hello', 10, '*') from dual;

/*
    2021년 01월 01일 이전에 입사한 모든 남자 직원들에게
    특별 상여금을 지급하려고 한다.
    특별상여금은 연봉의 200% 이다.
    해당 직원의 사원번호, 사원명, 관리자명, 상여금,
    주민번호, 아이디, 입사일을 출력하시오
    주민번호는 14자리를 잡아서 출력하되 생년월일
    성별까지만 출력하고
    오른쪽 빈칸은 * 로 출력한다.
    상여금은 10의 자리에서 반올림하여 출력하고
    10자리를 잡아서 출력하되 앞의 빈칸은 0으로 채운다.
*/
select eno, ename, 
(select m.ename from emp m where m.eno = e.mgr) 관리자명, 
lpad(round((salary*12*2)), 10, 0) 특별상여금,
rpad(substr(jumin, 1, 8), 14, '*') 주민번호, 
substr(email, 1, instr(email, '@')- 1) id, 
hiredate
from emp e
where TO_CHAR(HIREDATE, 'YYYY/MM/dd') < '2021/01/01' AND
substr(jumin, 8, 1) in (1, 3);

 

• concat(문자열1, 문자열2)  : 문자열 1과 2를 붙인다.