SQL INSTR

SELECT instr('abcdabcdabcd', 'a')
from dual;

SELECT instr('abcdabcdabcd', 'c')
from dual;
--
SELECT instr('abcdabcdabcd', 'c', 5)
from dual;

select instr('abcdabcdabcdabcd','a',5,2)
from dual;
--5번째자리부터 2번째에 만나는?

select instr('abcdabcdabcdabcd','a',5,4)
from dual;

-- 

--emp 테이블의 ename에서 'S'가 포함된 사원의 
--사원번호, 이름,부서코드, 입사일을 출력하시오
--(1)
select empno, ename, deptno, hiredate from emp
where ename like '%S%';

--(2)
select empno, ename, deptno, hiredate from emp
where instr(ename, 'S') >=1;

--입사 월 출력
select substr(hiredate,4,2) from emp;
--입사 년도 출력
select substr(hiredate,1,2) from emp;


--Q.82년도 입사한 사원 출력
select * from emp
where substr(hiredate,1,2) = '82';

--(2)
select * from emp
where hiredate >= '82/01/01' and hiredate <= '82/12/31';

--(3)
select * from emp
where hiredate between '82/01/01' and '82/12/31';

--E로 끝나는 사원 검색. 3가지방법
--(1) substr 사용
select ename from emp
where substr(ename,-1) = 'E';

--(2) instr 사용 ★★
select * from emp
where instr(ename, 'E', length(ename)) > 0;

--(3) like 사용
select ename from emp
where ename like '%E';

--3번째 자리가 R인 사원
--(1) like 
select ename from emp
where ename like '__R%';

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

--(3) instr 
select ename from emp
where instr(ename, 'R') = 3;

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