SQL 문자관련 명령어
함수에는 단일행 함수와 복수행 함수가 있는데
오늘은 단일행 함수를 알아보도록 하자!
1. 문자가 포함되어 있는지 체크
select * from EMPLOYEES where email like '%S%'; -- employees 테이블에서 메일에 S가 포함된 사원의 정보를 조회
select * from EMPLOYEES where email like '%S%S%'; -- 메일에 S를 둘 이상 포함하고 있는 사원의 정보를 조회.
select * from EMPLOYEES where email like '_SS%'; -- 두번째, 세번째 글자가 SS인 사원의 정보를 조회
select * from gogek where GOJUMIN like '_______1%'; -- gogek 테이블에서 like를 사용하여 성별이 남자인 사원을 조회
2. upper, lower : 대문자, 소문자 변환(명령어가 아닌 저장되어 있는 데이터는 대소문자를 가린다.)
select * from emp where lower(ename) = 'smith';
3. length, lengthb : 문자열의 글자수, 바이트 수
-- 아래에서 emp를 사용하면 결과가 12번 나온다.
-- 단일행 함수는 행의 수만큼 동작하기 때문이다.
select length('홍길동'), lengthb('홍길동') from dual;
4. instr : 문자열에서 문자열을 찾아 위치를 출력
select ename, instr(ename, 'L') from emp;
* instr()을 이용해서 뒤부터 위치를 찾자.
select instr('abcdabcabc', 'a', -1) from dual;
5.substr : 문자열을 잘라내는 함수
select substr('database', 1,2) from dual; -- 'database' 문자열의 첫번째 글자부터 두 개의 글자를 출력.
-- emp 테이블에서 hiredate, substr 을 이용해 1981년 입사자 정보 조회하시오.
select substr(hiredate, 1, 4) from emp;
select * from emp where substr(hiredate, 1, 4) = '1981';
-- emp 테이블에서 hiredate, substr, in 을 이용해 1980년, 1982년 입사자 정보를 조회하시오.
select * from emp where substr(hiredate, 1,4) in (1980, 1982);
6. reverse : 문자열을 뒤집는다.
-- 'd:/data/sales.xlsx'란 문자열에서 파일 이름만 출력하자.
select reverse(substr(reverse('c:/data/sales.xlsx'), 1, instr(reverse('c:/data/sales.xlsx'), '/')-1)) from dual;
select substr('c:/data/sales.xlsx',instr('c:/data/sales.xlsx', '/', -1)+1) from dual;
7. replace(문자열, 찾을 문자열, 바꿀 문자열)
select replace('You are not alone', 'You', 'We') from dual;
-- employees 테이블의 전화번호를 replace를 이용해 .을 제거하고 출력
desc employees;
select replace(phone_number, '.', ' ') from employees;
8. translate(문자열, 찾을 문자열, 바꿀 문자열)
--replace 는 문자열 단위로 바꾸지만 translate 는 문자단위로 바꾼다.
select TRANSLATE('You uoY', 'You', 'We ') from dual;
select replace(sal, '0123456789', '영일이삼사오륙칠팔구'), TRANSLATE(SAL,'0123456789', '영일이삼사오륙칠팔구') FROM EMP;
SELECT TRANSLATE('12345', '12345', 'X') FROM DUAL;
//변경할 문자를 지정하지 않으면, NULL이 되면서 출력에서 제거된다.
select translate('a02f3g5', ' 0123456789', ' ' ) from dual; -- 문자열에서 숫자를 제거할 때 사용.
select translate('a02f3g5', '1234567890'||'a02f3g5', '1234567890') from dual; -- 문자열에서 문자를 제거.
-- 숫자만 남기시오.
-- employees 의 전화번호를 translate 를 이용해 문자를 제거하고 출력하시오.
select translate(phone_number, '0123456789'||phone_number, '0123456789') from employees;
-- ltrim, rtrim : 제거
select ltrim(' ABCD') from dual; -- 왼쪽에서 지워라. (공백 지움)
select ltrim(' ABCD', 'A') from dual;
select ltrim('ABBACABCDDD', 'AB' ) from dual;
-- 문자열에서 A 나 B를 지우다가 A도, B도 아닌 문자를 만나면 중지한다.
9. round() : 반올림, trunc(): 소수점 아래를 잘라냄, ceil(a) : a보다 큰 가장 작은 정수, floor(a) : a보다 작은 가장 큰 정수
select round(3.5), trunc(3.5), ceil(3.5), floor(3.5) from dual;
select round(-3.5), trunc(-3.5), ceil(-3.5), floor(-3.5) from dual;
select round(123.456, 2) from dual;
select round(123.456, 1) from dual;
select round(123.456, 0) from dual;
select round(123.456, -1) from dual;
select round(123.456, -2) from dual;
-- emp 테이블에서 사번, 이름, 연봉을 출력하시오. 월급은 sal+comm이며
-- 연봉은 월급*12 로 계산하여 백의 자리에서 반올림하여 출력하시오.
-- 연봉 내림차순으로 출력하시오.
select empno, ename, round(nvl(sal+comm,sal)*12, -3) 연봉 from emp order by 연봉 desc;
-- 실행순서가 from -> where -> select -> order by 순으로
-- emp 테이블에서 이름과 근무일수 출력
select ename, trunc(sysdate-hiredate)||'일' 근무일수 from emp;
-- emp 테이블에서 이름과 근무년수를 출력
select ename, trunc(trunc(sysdate-hiredate)/365, 0 )||'년 ' 근무년수 from emp;