오라클 SQL 문법 공부6 - SUBSTR, INSTR, SYSDATE

서론

이번에 나열한 함수는 자주 쓰이는 함수 SUBSTR, INSTR이다. 문자열을 가공하거나 특정 문자열 비교를 할때 사용되며 많이 익숙해져야 할 함수라고 생각한다.

본론

SUBSTR

문자열을 자를때 사용된다.

/*SUBSTR() 함수 문자열에서 원하는 문자 추출
 --SUBSTR('문자열'또는 컬럼명, m [,n])
 --문자열 중 지정한 위치(m)에서 지정한 길이(n)만큼 문자추출
 --n은 생략 가능하며 생략 시 맨 끝까지 추출
 --m이 음수일 경우에는 글자 뒤에서부터 개수만큼이 출발지점
 --글자 셀때 방향이 바뀌지 않음

select substr('abcdefg', 1, 1),  --첫번째 자리 부터 1글자 추출 : 'a'
       substr('abcdefg', 2, 1),    --두번째 자리부터 1글자 추출 : 'b'
       substr('abcdefg', 2, 2),    --두번째 자리부터 2글자 추출 : 'bc'
       substr('abcdefg', -2, 3)   --오른쪽부터 2번째 자리부터 3글자 추출 : 'fg'
--오른쪽 2번째 자리부터 시작하기 때문에 f부터 추출하는데 g가 끝이므로 2글자만 추출 후 더 이상 추출 안됨
from dual;

select name,
       height
  from student
 where substr(jumin, 7, 1) = '2';
 --주민번호 7번째 성별을 뜻하는 자리에서 여자만 찾기

SELECT SUBSTR(SYSDATE, 1, 4) FROM DUAL;
--년도 추출, 해당 값이 비정상적으로 나올 수도 있는데 그 이유는 날짜포맷이 다르기 때문이다.
오라클을 기본 설치 했다면 2017년 9월 기준 2017이 아닌 17/0 이런식으로 나올 것이다. */
alter session set nls_date_format = 'YYYY-MM-DD HH24-MI-SS';
--나중에 문법 공부 끝나고 다른 부분에서 다룰 부분인데 날짜 포맷을 현재 세션에만
위와 같이 변경한 후 실행해 보면 정상적으로 년도만 나올 것이다*/
--실제 데이터 포맷은 년도가 2자리로 표시되지만 DB개발툴에 따라 
--SELECT SYSDATE FROM DUAL; 해보면 4자리로 보일 수도 있는데 그건 개발툴의 설정에서 변경할 수 있다

여기서 중요한 점은 문자열을 자르는 것이기 때문에 비교하거나 할때 역시 문자열로 비교를 해야 한다.

음수로 오른쪽부터 해당 숫자만큼 이동 후 자를때는 계속 왼쪽으로 세어 나가는 것이 아니라 해당 위치부터 오른쪽으로 개수만큼 자르는 것이다. 하지만 문자열의 범위를 넘어가게 되면 남은 것만 추출한다.

특히 날짜를 자를때 자를때도 사용 하는데 (특정년도를 뽑는다 던가 특정 일) 날짜데이터 타입을 넣어도 SUBSTR 함수는 알아서 문자열로 변경해서 자르게 된다.

INSTR

지정한 문자열이 몇번째에 있는지 찾는 함수이다.

--INSTR()함수 특정 글자의 위치 찾기
--INSTR('문자열' 또는 컬럼, 찾는 글자 [,m][,n])
--문자열 중 지정한위치 m에서 특정문자 (char2)가 특정 순서(n)로 나타나는 문자열의 위치를 반환
--찾는 글자를 특정 위치 m부터 찾기 시작해 n 번째에 있는거 찾는거임
--m 생략시 처음부터 시작
--m이 음수일 경우는 맨 끝에서 왼쪽 방향으로 찾기 시작
--n 생략 시 최초 위치를 반환
--찾고자하는 문자가 없으면 0 리턴

select instr('121416181', '1', -2, 2) from dual;
--오른쪽에서 두번째 자리부터 찾을 거고 해당 위치에서 왼쪽으로 2번째 '1'을 찾으면 5의 값이 나온다.

SUBSTR에서는 음수 값일 경우 오른쪽에서 해당 숫자만큼 이동 후 오른쪽으로 세어 간다면 INSTR의 음수값은 오른쪽에서 해당 숫자만큼 이동 후 계속해서 왼쪽으로 찾아간다. 그리고 결과 값은 왼쪽부터 세었을때의 자리수이다. 헷갈리니 이것저것 테스트를 하면서 익히는게 좋겠다. 특정 단어 앞에 까지의 데이터를 추출하고 싶다면 INSTR로 특정단어의 위치를 찾고, SUBSTR로 해당 위치까지 추출하는 식으로 SUBSTR과 INSTR을 같이 사용하는 경우가 많다.

SYSDATE

SYSDATE는 현재 날짜를 표현하는 함수이다. 시간까지 표현된다.

SELECT SYSDATE FROM DUAL;

실습

--1. emp 테이블에서 커미션을 받지 않는 사원의 급여를 10% 인상하여 표시하되, 다음의 형식으로 출력하여라
--The salary of SMITH after a 10% raise is 2000
select 'The salary of ' || ename || ' after a 10% raise is ' || SAL * 1.1 AS TEST
  from EMP
 where comm is null;

--2. emp 테이블에서 급여(sal)와 커미션(comm) 합계가 10% 인상된 급여보다 많은 사원의 이름, 급여 및 커미션을 표시하도록 한다.
--단 comm이 null이면 무시
select ename,
sal,
comm
from emp
where sal + comm > sal * 1.1 and comm is not null;

--3. emp 테이블에서 이름의 3번째가 R이며 이름의 길이가 6글자인 사원의 정보를 출력하여라
select *
from EMP
where SUBSTR(ename, 3, 1) = 'R'  AND LENGTH(ename) = 6;
--아래와 같이 like를 써도 좋다
select *
from EMP
where ename LIKE '__R___%';


--4. student 테이블을 참조해서 전화번호에서 지역번호를 추출하여라
--단, ) 없이 지역번호만 추출되도록 한다.
select SUBSTR(tel, 1, INSTR(tel, ')') - 1)
from student;


--5. student 테이블을 이용하여 학생이름이 'ㅅ'으로 시작하는 학생의 이름, 학년, 출생년도를 출력하여라
--첫 자음이 'ㅅ'인 학생
select name,
grade,
'19' || substr(birthday, 1, 2)
from STUDENT
where substr(name, 1) >= '사' AND substr(name, 1) < '아'  ;

결론

실습문제는 2017/09/18 - [공부/Oracle] - 오라클 SQL 문법 공부4 - 기본문법 실습 이 글에서 해당 SQL을 실행시켜 넣은 데이터들이다. 생각보다 많이 생각해봐야지 풀수 있는 문제이니 꼭 실습해보자.